Function Performance question

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.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
"mvp" <mvp@.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.
>