StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuery I need to be sped up
text
Body
copied!<p>I have this query in SQL Server 2005:</p> <pre><code>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) </code></pre> <p>This query is taking too long to run, and I know the problem is the UDF - (dbo.fncIsAllPointsDelivered(J.JobID) = 1) -.</p> <p>The SQL for the UDF is here:</p> <pre><code> 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 </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload