Function Performance question

25 Aug 2005 6:17 PM

mvp

I do have one store procedure which does insert into one table

CREATE PROCEDURE StoreProc1
AS
DECLARE testcursor CURSOR FOR
   SELECT col1
   FROM table
   WHERE Id = @ID

   OPEN testcursor
   FETCH NEXT FROM cursor INTO @col1

   WHILE @@FETCH_STATUS = 0
   BEGIN

      --Here i have to use cursor because i am doing some calculation
      --here based value of co11
     --And then insert into one table

    INSERT INTO TESTTABLE
     (id,transactiondate....) values (@value1,@value2........)
      FETCH NEXT FROM testcursor INTO @col1

   END

CLOSE testcursor
   DEALLOCATE testcursor

This StoreProc1 i am running every night and which insert approx 500,000
records into TESTTABLE..Now I have a very simple function on TESTTABLE
which is as following..which i use in other store procedures....

CREATE FUNCTION TestFunction
(@ID as INT,@dt1 datetime,@dt2 datetime)
returns money
AS
BEGIN
DECLARE @retmoney money
SELECT @retmoney = sum(amount)
FROM TESTTABLE
WHERE transactiondate between @dt1 and @dt2 and id = @Id
and    categoryid not in ('1','2')

RETURN @retmoney
END

so what happen after running StoreProc1 every night...(which insert into
500 K records into TESTTABLE.. My function TestFunction becomes so slow.. it
takes 10 second to run and if i run   query of that function

SELECT @retmoney = sum(amount)
FROM TESTTABLE
WHERE createddate between @dt1 and @dt2 and id = @Id
and    categoryid not in ('1','2')
it get execute in only o seconds...

so why if i run that function it takes long and if i run that same query it
is fast...
Pls let me know.

25 Aug 2005 9:55 PM

Hugo Kornelis

On Thu, 25 Aug 2005 11:17:04 -0700, mvp wrote:

(snip)

>so what happen after running StoreProc1 every night...(which insert into
>500 K records into TESTTABLE.. My function TestFunction becomes so slow.. it
>takes 10 second to run and if i run query of that function

Hi mvp,

Google for "parameter sniffing" to get the explanation.

Some possible cures (test to see which works best for you):

1. Add "WITH RECOMPILE" to the CREATE FUNCTION statement; or
2. Use local variables in the function.

Also, a comment on this:

>      --Here i have to use cursor because i am doing some calculation
>      --here based value of co11
>     --And then insert into one table
>
>    INSERT INTO TESTTABLE
>     (id,transactiondate....) values (@value1,@value2........)
>      FETCH NEXT FROM testcursor INTO @col1

It's often possible to replace a cursor with a set-based function. Doing
calculations is in itself not enough reason to go for a cursor (though
SOME calculations are). You might want to check www.aspfaq.com/5006,
then post here if you want the guru's in this group to have a look at
your code and try to make a set-based version.

OTOH, if the code is running during off-hours and performance is not
relevant at all, you might also decide to say: "if it ain't broke, don't
fix it".

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

27 Aug 2005 7:39 PM

John Bell

Hi

You may have perform maintainance on this table to update the indexes or
statistics as they could be fragmented or out of date. See DBCC SHOWCONTIG,
DBCC DBREINDEX and UPDATE STATISTICS in books online

John

Show quote

"mvp" <m**@discussions.microsoft.com> wrote in message
news:6360AC44-D1D9-4D05-91B3-201C58E1B543@microsoft.com...
>I do have one store procedure which does insert into one table
>
> CREATE PROCEDURE StoreProc1
> AS
> DECLARE testcursor CURSOR FOR
>   SELECT col1
>   FROM table
>   WHERE Id = @ID
>
>   OPEN testcursor
>   FETCH NEXT FROM cursor INTO @col1
>
>   WHILE @@FETCH_STATUS = 0
>   BEGIN
>
>      --Here i have to use cursor because i am doing some calculation
>      --here based value of co11
>     --And then insert into one table
>
>    INSERT INTO TESTTABLE
>     (id,transactiondate....) values (@value1,@value2........)
>      FETCH NEXT FROM testcursor INTO @col1
>
>   END
>
> CLOSE testcursor
>   DEALLOCATE testcursor
>
> This StoreProc1 i am running every night and which insert approx 500,000
> records into TESTTABLE..Now I have a very simple function on TESTTABLE
> which is as following..which i use in other store procedures....
>
> CREATE FUNCTION TestFunction
> (@ID as INT,@dt1 datetime,@dt2 datetime)
> returns money
> AS
> BEGIN
> DECLARE @retmoney money
> SELECT @retmoney = sum(amount)
> FROM TESTTABLE
> WHERE transactiondate between @dt1 and @dt2 and id = @Id
> and categoryid not in ('1','2')
>
> RETURN @retmoney
> END
>
>
> so what happen after running StoreProc1 every night...(which insert into
> 500 K records into TESTTABLE.. My function TestFunction becomes so slow..
> it
> takes 10 second to run and if i run   query of that function
>
> SELECT @retmoney = sum(amount)
> FROM TESTTABLE
> WHERE createddate between @dt1 and @dt2 and id = @Id
> and categoryid not in ('1','2')
> it get execute in only o seconds...
>
> so why if i run that function it takes long and if i run that same query
> it
> is fast...
> Pls let me know.
>