Query I need to be sped up

Question

I have this query in SQL Server 2005:

SELECT J.JobID, 
       dbo.tblCustomers.Name AS CustomerName, 
       J.CustomerJobNumber, 
       J.JobName, 
       (CASE WHEN [tblCustomers].[CoreCust] = 0 THEN 'AUXILIARY' ELSE 'CORE' END) AS Department, 
       J.JobStatusID, 
       dbo.tblJobTypes.JobType
  FROM dbo.tblJobs (NOLOCK) AS J 
INNER JOIN dbo.tblCustomers (NOLOCK) ON J.CustomerID = dbo.tblCustomers.CustomerID
INNER JOIN dbo.tblJobTypes (NOLOCK) ON J.JobTypeID = dbo.tblJobTypes.JobTypeID
INNER JOIN dbo.tblDepartments (NOLOCK) ON J.DepartmentId = dbo.tblDepartments.DepartmentID
WHERE (J.Closed = 0) 
  AND (J.Invoiced = 0) 
  AND (J.Active = 1) 
  AND (dbo.fncIsAllPointsDelivered(J.JobID) = 1) 
  AND (J.DepartmentId <> 2)

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 [dbo].[tblLoadDetails] (NOLOCK)
     WHERE JobId = @JobId

    SELECT @TrackingCount = COUNT(*)
      FROM [dbo].[tblLoadDetails] (NOLOCK)
     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. I am hoping some of you SQL gurus will be able to help me.

Answer 1

I'm working this from the top of my head, so I haven't tried this out. But I think you could do this to remove the function. Replace the call to the function with these two clauses. This is assuming that 'Delivered' is a BIT field:

AND EXISTS (SELECT 1 FROM tblLoadDetails WHERE JobID = J.JobID)
AND NOT EXISTS (SELECT 1 FROM tblLoadDetails WHERE JobID = J.JobID AND Delivered = 0)

The AND EXISTS covers the UDF's @DetailCount > 0 check; the AND NOT EXISTS then covers the @DetailCount = @TrackingCount, the assumption I'm making is that you're looking to see if the job exists and everying to do with that job has been delivered. so if there's even one thing that hasn't been delivered, it needs to be excluded.

As mentioned: from top of head, and thus not tested or not profiled. I think I've got the logic right. If not, it should be a simple variation thereof.

Answer 2

SELECT  *
FROM    tblJobs j
INNER JOIN
        tblCustomers c
ON      c.CustomerID = J.CustomerID
INNER JOIN
        tblJobTypes jt
ON      jt.JobTypeID = J.JobTypeID
INNER JOIN
        tblDepartments d
ON      d.DepartmentID = J.DepartmentId
WHERE   J.Closed = 0
        AND J.Invoiced = 0
        AND J.Active = 1
        AND J.DepartmentId <> 2
        AND J.JobID IN
        (
        SELECT  JobID
        FROM    tblLoadDetails
        )
        AND J.JobID NOT IN
        (
        SELECT  JobID
        FROM    tblLoadDetails
        WHERE   Delivered <> 1
        )

Create a composite index on these fields:

tblJobs (Closed, Invoiced, Active) INCLUDE (DepartmentID)

If your tblLoadDetails.Delivered is a bit field, then create the following index:

tblLoadDetail (JobID, Delivered)

and rewrite the last condition as this:

SELECT  *
FROM    tblJobs j
INNER JOIN
        tblCustomers c
ON      c.CustomerID = J.CustomerID
INNER JOIN
        tblJobTypes jt
ON      jt.JobTypeID = J.JobTypeID
INNER JOIN
        tblDepartments d
ON      d.DepartmentID = J.DepartmentId
WHERE   J.Closed = 0
        AND J.Invoiced = 0
        AND J.Active = 1
        AND J.DepartmentId <> 2
        AND
        (
        SELECT  TOP 1 Delivered
        FROM    tblLoadDetails ld
        WHERE   ld.JobID = j.JobID
        ORDER BY
                Delivered
        ) = 1