Optimizing Query

Hal Levy · October 30th, 2003, 11:21 AM

I have a Stored Proc that is used to generate a report. It's taking, on average, 1:40 to run. Of course, this is causing timeouts with the web site that is querying it.

Anyone have any advice on how I can improve this Query?

Code:

    SET NOCOUNT ON 
    SELECT t_CallInstance.PDIControlID, t_Target.PhysicianFirstName, t_Target.PhysicianLastName, 
t_DetailOrder.ProductID, t_TargetFrequency.Frequency, t_DetailOrder.DetailOrder
FROM         t_TargetFrequency INNER JOIN
                      t_Target ON t_TargetFrequency.PdiControlID = t_Target.PDIControlID INNER JOIN
                      t_DetailOrder ON t_TargetFrequency.ProductID = t_DetailOrder.ProductID INNER JOIN
                      t_CallInstance ON t_Target.PDIControlID = t_CallInstance.PDIControlID AND t_DetailOrder.CallInstanceID = t_CallInstance.CallInstanceID AND 
                      t_DetailOrder.CallInstanceID = t_CallInstance.CallInstanceID
WHERE     (t_CallInstance.TerritoryID = @terrid) AND (t_CallInstance.CallDate between @startdt and @enddt OR t_CallInstance.CallDate = @startdt OR t_CallInstance.CallDate = @enddt) 
UNION ALL
SELECT DISTINCT
                      t_target.PDIControlID, t_target.PhysicianFirstName, t_target.PhysicianLastName, t_TargetFrequency.ProductID, t_TargetFrequency.Frequency, 0 as DetailOrder
FROM         t_Target INNER JOIN t_CallInstance on (t_CallInstance.PDIControlID <> t_Target.PDIControlID) AND (t_CallInstance.PDIControlID <> t_Target.PDIControlID) INNER JOIN
              t_TargetFrequency ON t_target.PDIControlID = t_TargetFrequency.PdiControlID 
                      CROSS JOIN t_Location lc 
WHERE     (lc.Territory = @terrid ) AND (t_target.PDIControlID = lc.PDIControlID) AND (t_Target.PDIControlID NOT IN(SELECT PDIControlID from t_CallInstance)) 
ORDER BY t_Target.PhysicianLastName, t_CallInstance.PDIControlID ASC 
    RETURN

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Jeff Mason · October 30th, 2003, 02:57 PM

Just a few efficiency observations, in no particular order:

1. That CROSS JOIN can do it to you.

2. First thing to look at is the query execution plan. See if any table scans are being done. If so, make sure that indexes and/or foreign key constraints are present on all columns involved in the JOIN expressions and WHERE clauses. This has to be tempered, though, on how much updating/inserting/deleting is going on in these tables, as indexes must be maintained whenever these operations are done, so it you have a lot of indexes, these operations will slow down. It's a balancing act.

3. By default the UNION operator removes duplicate rows, and UNION ALL keeps them, so I find it interesting that you are using SELECT DISTINCT to remove duplicates, then using UNION ALL to keep them.

4. The BETWEEN operator is inclusive of its end points, so your WHERE clauses have redundant expressions in them.

Beyond this cursory look, we'll have to get into table structure and what you are trying to accomplish...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hal Levy · October 31st, 2003, 11:50 AM

Jeff,

This is a job that was outsourced before I got here. I am not particularly happy with the table design or the quality of the stored procs. Of course, I am not a DBA so optimization and complex querys are beyond me.

They re-did the SP overnight and it now runs in 2 seconds (versus 2 minutes). When looking at the execution plan for the old SP there was a point where there was 40 MILLION rows.. With the data quantity I had, this was clearly a cartesian product from a bad join...

Some of the points you make, however, are still valid.. I am pasting the new SP here...

Perhaps some day I'll be good enough with SQL to follow this :)

Code:

CREATE  PROCEDURE dbo.PDIDC_rpt_OptimalFreq

    (
        @startdt datetime,
        @enddt datetime,
        @terrid nvarchar(8)
    )

AS
    SET NOCOUNT ON 
    SELECT t_CallInstance.PDIControlID, t_Target.PhysicianFirstName, t_Target.PhysicianLastName, 
t_DetailOrder.ProductID, t_TargetFrequency.Frequency, t_DetailOrder.DetailOrder
FROM         t_TargetFrequency INNER JOIN
                      t_Target ON t_TargetFrequency.PdiControlID = t_Target.PDIControlID INNER JOIN
                      t_DetailOrder ON t_TargetFrequency.ProductID = t_DetailOrder.ProductID INNER JOIN
                      t_CallInstance ON t_Target.PDIControlID = t_CallInstance.PDIControlID AND t_DetailOrder.CallInstanceID = t_CallInstance.CallInstanceID AND 
                      t_DetailOrder.CallInstanceID = t_CallInstance.CallInstanceID
WHERE     (t_CallInstance.TerritoryID = @terrid) AND (t_CallInstance.CallDate between @startdt and @enddt OR t_CallInstance.CallDate = @startdt OR t_CallInstance.CallDate = @enddt) 
UNION ALL
SELECT DISTINCT
                      t_target.PDIControlID, t_target.PhysicianFirstName, t_target.PhysicianLastName, t_TargetFrequency.ProductID, t_TargetFrequency.Frequency, 0 as DetailOrder
FROM         t_Target INNER JOIN
              t_TargetFrequency ON t_target.PDIControlID = t_TargetFrequency.PdiControlID 
                      CROSS JOIN t_Location lc 
WHERE     (lc.Territory = @terrid ) AND (t_target.PDIControlID = lc.PDIControlID) AND (t_Target.PDIControlID NOT IN(SELECT t_CallInstance.PDIControlID from t_CallInstance where (t_CallInstance.TerritoryID = @terrid) AND (t_CallInstance.CallDate between @startdt and @enddt OR t_CallInstance.CallDate = @startdt OR t_CallInstance.CallDate = @enddt))) 
ORDER BY t_Target.PhysicianLastName, t_CallInstance.PDIControlID ASC 
    RETURN
GO

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee