Function performance very slow

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)

Function performance very slow

Author

Topic

alfatat
Starting Member

1 Post

Posted - 2006-03-25 : 05:10:12

Hi!
Guys I am pasting the code of a function which is taking more than 3 mins to get executed.

CREATE FUNCTION dbo.getCOStatus (@ooid as integer )
RETURNS decimal AS
BEGIN

declare @Status as decimal
declare @qtyo as decimal
declare @qtyd as decimal

select @qtyo = sum(cod.quantityOrdered)
from codetails cod
inner join coheader ch on ch.coid = cod.coid
Inner join ooheader oh on ch.coid = oh.coid
Where ooid = @ooid

select @qtyd = isnull(sum((p.CartonTo - p.Cartonfrom + 1)* p.CartonQty),0)
from PakingListDetails p
Inner Join codetails cod on cod.CODetailID = p.CODetailID
Inner Join coheader ch on ch.coid = cod.coid
Inner join ooheader oh on ch.coid = oh.coid
Where ooid = @ooid
if (@qtyd = 0)
set @Status = 3 -- Open
Else if (@qtyd > 0)
Begin
if (@qtyo < @qtyD)
set @Status = 2 -- Closed
else if (@qtyo - @qtyD = 0)
set @Status = 0 -- Closed
else
set @Status = 1 -- Partially Complete
end
return @Status
END

there are around 5000 rows in each of the tables employed by the queries.

Just get back to me as soon coz my application is imp.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-25 : 06:02:17

I dont know whether above the output but you can just check the script for the Case when query .. .. directly in the query instead of using if codition after that



CREATE FUNCTION dbo.getCOStatus (@ooid as integer )
RETURNS decimal AS
BEGIN

declare @Status as decimal
declare @qtyo as decimal
declare @qtyd as decimal

select @Status  = Case When (sum(cod.quantityOrdered)) = 0 Then 3  --Open 
 When (sum(cod.quantityOrdered)) > 0  Then 
	Case When ((sum(cod.quantityOrdered)) < isnull(sum((p.CartonTo - p.Cartonfrom + 1)* p.CartonQty),0)) Then  2 -- Closed
	Case When ((sum(cod.quantityOrdered)) - isnull(sum((p.CartonTo - p.Cartonfrom + 1)* p.CartonQty),0)) = 0 Then 0 -- Closed
	Else 1 -- Partially Open	
End 
End 
from codetails cod
inner join coheader ch on ch.coid = cod.coid
Inner join ooheader oh on ch.coid = oh.coid
Inner Join PakingListDetails p on cod.CODetailID = p.CODetailID
Where ooid = @ooid

return @Status


END

Hope this works for you.

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-03-27 : 07:31:44

Can you post the execution plan of the function from a sample run. Indices (or lack thereof) usually are the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources