low speed in function when we have a lots of recor

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 2005 Forums

Transact-SQL (2005)

low speed in function when we have a lots of recor

Author

Topic

pershian2020
Starting Member

1 Post

Posted - 2009-05-04 : 08:48:57

i use a function from my select ,function return avg. when we have a few data it works. but when we have a lots of data for example 500000 record, it doesnt work. what should i do? can i use anything rather than function.

SELECT xDonerOmorId_fk , (dbo.Fn_Test2(xDonerOmorId_fk))

ALTER FUNCTION [dbo].[Fn_Test2]
(
-- Add the parameters for the function here
@xDonerOmorId int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Avg int

SELECT @Avg=AVG(dbo.vwReq_Delay.xCurrDelay)
FROM dbo.Req_Vw_StaticBase INNER JOIN
dbo.vwReq_Delay ON dbo.Req_Vw_StaticBase.xRequestId_pk = dbo.vwReq_Delay.xRequestId_fk

where (xDonerOmorId_fk=@xDonerOmorId) and (xCurrDelay<=0)
-- Return the result of the function
RETURN @Avg

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-04 : 09:10:54

dont use a function for this. Have your main select modified to something like ..

SELECT a1.xDonerOmorId_fk , avg(a2.xCurrDelay) 
from
yourtable a1 join Req_Vw_StaticBase a2 on a2.xRequestId_pk = a1.xRequestId_fk
group by
a1.xDonerOmorId_fk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources