Archive for October 12th, 2009
From Stack Overflow:
I have this query in SQL Server 2005:SELECT * FROM tblJobs AS j -- joining other tables here WHERE Closed = 0 AND Invoiced = 0 AND Active = 1 AND DepartmentId <> 2 AND dbo.fncIsAllPointsDelivered(JobID) = 1
This query is taking too long to run, and I know the problem is the UDF:
dbo.fncIsAllPointsDelivered(J.JobID) = 1
The SQL for the UDF is here:DECLARE @DetailCount int DECLARE @TrackingCount int SELECT @DetailCount = COUNT(*) FROM tblLoadDetails WHERE JobId = @JobId SELECT @TrackingCount = COUNT(*) FROM tblLoadDetails WHERE JobId = @JobId AND Delivered = 1 IF (@DetailCount = @TrackingCount AND @DetailCount > 0) RETURN 1 RETURN 0
All of this runs blazingly fast unless the job has a large number of load details in it.
I am trying to think of a way to either make the UDF faster or get rid of the need for the UDF, but I am at a loss.
UDF is definitely a problem here, since the conditions based on the UDF's are not sargable, that is an index cannot be used to search for them.
Let's look closed into this UDF.
@JobID, the function searches the load details for this job. For the function to succeed, all load details should have the state of
Delivered, and there must be at least one load detail for this job.
The UDF can be rewritten using
NOT IN conditions. The first condition makes sure that there is at least one load detail for the given job, the second one ensures that all load details have state
Let's create a sample table and see how it works:
Read the rest of this entry »