number of function calls?//crosspost on developer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

number of function calls?//crosspost on developer

Author

Topic

Dargon
Starting Member

26 Posts

Posted - 2006-01-31 : 11:55:22

Hi all,
I have user defined function, pretty simple one, which gets called from set of stored procedures. I wonder, if it is any way to figure out how many times function called when running an application.
I can not update any counters from function. Is it any way to see it using SQL profiler of other tools?
Just in case, here is function itself, it just converts quantity.

Thanks, Dargon

ALTER FUNCTION [dbo].[ConvertQuantity]
( @qty AS FLOAT, @from_unit AS INT, @to_unit AS INT )
RETURNS FLOAT
AS
BEGIN
DECLARE @from_scaler AS FLOAT
DECLARE @from_offset AS FLOAT
DECLARE @to_scaler AS FLOAT
DECLARE @to_offset AS FLOAT
DECLARE @converted AS FLOAT

IF ( 0 > @to_unit ) -- don't do conversion if target unit id is -1
BEGIN
RETURN @qty
END
-- else ...
SET @converted = @qty

SELECT @from_scaler=Scaler, @from_offset=Offset FROM UOM WHERE [UOM].UoM=@from_unit
SELECT @to_scaler=Scaler, @to_offset=Offset FROM UOM WHERE [UOM].UoM=@to_unit

IF ( 0.0 != @to_scaler )
BEGIN
SET @converted = ((( @qty + @from_offset ) * @from_scaler )/@to_scaler ) - @to_offset
END
RETURN @converted
END

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-31 : 12:16:53

Hi

Can't you embed the Counter in your application? Everytime the app calls the SP you increase your counter by 1

N

The revolution won't be televised!

Dargon
Starting Member

26 Posts

Posted - 2006-01-31 : 12:28:51

The problem is that I have set of SP(about 15) which call this function, some of them called many times. I thought I can track each call to this UDF from database, but did not found how.

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-31 : 12:41:56

All 15 SPs call the UDF from the Frontend?
If it's true then what I said would apply

I can't think of a way from the actual SPs

N

The revolution won't be televised!

Dargon
Starting Member

26 Posts

Posted - 2006-01-31 : 13:03:32

No, is could be a long track...I do not want go too deep into details, but let's just think that it is tree-like structure
SP1->call Sp_a,SP_b,...-> SP1..15->use UDF to convert value. It is kind of difficult to predict how many levels are between SP called from Frontend and actual SP which uses UDF. The reasom why I try to get this info is performance issue application has, so I try to cut edges where it is possible. This function could be one of them if I'll see that it called too many times.
Dargon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources