Solved

How do you change query using temp tables to run quickly ?

Posted on 2004-08-14
4
222 Views
Last Modified: 2012-05-05
I am having trouble changing the query below to run quickly. This query places all of the intermediate values in temporary tables when they really need to be kept in memory but I do not have a lot of experience building queries to run at the highest speed possible. Any suggestions would be appreciated.
ALTER Procedure dbo.spWeekReport2 (@WeekContainingThisDate datetime,
@FirstInitial char,
@LastName varchar(50))
AS
SET NOCOUNT ON
DECLARE @iError INT

IF @WeekContainingThisDate is null
BEGIN
RAISERROR(' WeekContainingThisDate is a Required Parameter.',11,1)
RETURN @@error
END

IF @FirstInitial is null
BEGIN
RAISERROR(' FirstInitial is a Required Parameter.',11,1)
RETURN @@error
END

IF @LastName is null
BEGIN
RAISERROR(' LastName is a Required Parameter.',11,1)
RETURN @@error
END

DECLARE @start DATETIME
DECLARE @stop DATETIME
/* SET @start = '20040626'  */
SET @start = @WeekContainingThisDate

/* *****************************************
-- Change date range given to start on Saturday
--**************************************** */
DECLARE @StartDay TINYINT
SET @StartDay = DATEPART(WEEKDAY, @start)

While @StartDay <> 7
BEGIN
  SET @start = @start - 1
  SET @StartDay = DATEPART(WEEKDAY, @start)
END

DECLARE @EmployeeID INT
SET @EmployeeID = (Select EmployeeID From dbo.tblEmployee E  Where
UPPER(Left(FirstName,1)) = UPPER(@FirstInitial) AND
UPPER(LastName) = UPPER(@LastName))

/* *****************************************
-- Calc. 3 Routes where Download date within date range given
--**************************************** */
DECLARE @RouteID1 INT
SET @RouteID1 = (Select Distinct RD.RouteID From dbo.tblEmployee E  INNER JOIN
Dbo.tblRouteDownload RD ON RD.EmployeeID = E.EmployeeID  INNER JOIN dbo.tblRouteLoc RL ON RL.RouteID = RD.RouteID INNER JOIN
dbo.tblRoute RO ON RO.RouteID = RD.RouteID INNER JOIN
Dbo.tblLocService LS ON (LS.LocationID = RL.LocationID AND
LS.EmployeeID = E.EmployeeID)
Where E.EmployeeID = @EmployeeID AND
RO.StatusCode > 0 AND
 (LS.BeginDate between @start and (@stop+1)))

DECLARE @RouteID2 INT
SET @RouteID2 = (Select Distinct RD.RouteID From dbo.tblEmployee E  INNER JOIN
Dbo.tblRouteDownload RD ON RD.EmployeeID = E.EmployeeID  INNER JOIN dbo.tblRouteLoc RL ON RL.RouteID = RD.RouteID INNER JOIN
dbo.tblRoute RO ON RO.RouteID = RD.RouteID INNER JOIN
Dbo.tblLocService LS ON (LS.LocationID = RL.LocationID AND
LS.EmployeeID = E.EmployeeID)
Where E.EmployeeID = @EmployeeID AND
RO.StatusCode > 0 AND
 (LS.BeginDate between @start and (@stop+1)) AND
 RD.RouteID <> @RouteID1)

DECLARE @RouteID3 INT
SET @RouteID3 = (Select Distinct RD.RouteID From dbo.tblEmployee E  INNER JOIN
Dbo.tblRouteDownload RD ON RD.EmployeeID = E.EmployeeID  INNER JOIN dbo.tblRouteLoc RL ON RL.RouteID = RD.RouteID INNER JOIN
dbo.tblRoute RO ON RO.RouteID = RD.RouteID INNER JOIN
Dbo.tblLocService LS ON (LS.LocationID = RL.LocationID AND
LS.EmployeeID = E.EmployeeID)
Where E.EmployeeID = @EmployeeID AND
RO.StatusCode > 0 AND
 (LS.BeginDate between @start and (@stop+1)) AND
  RD.RouteID <> @RouteID1 AND
 RD.RouteID <> @RouteID2)
 
DECLARE @LocAdded VarChar(20)
SET @LocAdded = 'Added'

DECLARE @LocRemoved VarChar(20)
SET @LocRemoved = 'Removed'

/* *****************************************
-- Calc. LocationID sets for Routes where Download date within date range given
--**************************************** */
CREATE TABLE #temp_results3 ( RouteID INT, DwnLdDate DateTime,
LocationId INT, GuideId INT, ChangeoutDate DateTime,
DateAdded DateTime, DateRemoved DateTime, ServiceThreshold INT,
DateOfServiceBefore DateTime, DateOfServiceAfter DateTime)
SET NOCOUNT ON
INSERT INTO #temp_results3
SELECT     RL.RouteID As RouteID,
Max(RD.DdateTime) As DwnLdDate, LIC.LocationId,
LIC.GuideId, GD.ChangeoutDate,
Null As DateAdded, Null As DateRemoved,
LIC.ServiceThreshold,
Null As DateOfServiceBefore, Null As DateOfServiceAfter
FROM         dbo.tblLocTransaction LT INNER JOIN
dbo.tblLocInvCur LIC ON (LIC.LocationID = LT.LocationID AND
LIC.GuideId = LT.GuideId)
          INNER JOIN
dbo.tblGuide GD ON GD.GuideID = LIC.GuideId
          Inner Join
dbo.tblRouteLoc RL ON RL.LocationID = LIC.LocationID
          INNER JOIN
Dbo.tblRouteDownload RD ON RD.RouteID = RL.RouteID
          INNER JOIN
Dbo.tblLocService LS ON (LS.LocationID = LIC.LocationID AND
LS.EmployeeID = RD.EmployeeID)
Where RD.EmployeeID = @EmployeeID AND
          RD.DdateTime < (@stop +1) AND
(LT.TransDate between @start and (@stop+1)) AND
(LIC.CloseDate IS NULL OR
                      LIC.CloseDate > @start)
GROUP BY RL.RouteID, LIC.LocationId, LIC.GuideId, GD.ChangeoutDate,
                   LIC.ServiceThreshold
UNION ALL
SELECT     RL.RouteID As RouteID,
Max(RD.DdateTime) As DwnLdDate, RL.LocationId,
Null As GuideId, Null As ChangeoutDate,
Null As DateAdded, Null As DateRemoved, LIC.ServiceThreshold,
Null As DateOfServiceBefore, Null As DateOfServiceAfter
FROM   dbo.tblLocInvCur LIC INNER JOIN
dbo.tblRouteLoc RL ON RL.LocationID = LIC.LocationID
          INNER JOIN
Dbo.tblRouteDownload RD ON RD.RouteID = RL.RouteID
Where RD.EmployeeID = @EmployeeID AND
(RD.RouteID = @RouteID1 OR
RD.RouteID = @RouteID2 OR
RD.RouteID = @RouteID3)
GROUP BY RL.RouteID, RL.LocationId, LIC.ServiceThreshold
UNION ALL
SELECT     RL.RouteID As RouteID,
Null As DwnLdDate, RL.LocationId,
Null As GuideId, Null As ChangeoutDate,
Null As DateAdded, Null As DateRemoved, Null As ServiceThreshold,
Max(LT.TransDate) As DateOfServiceBefore,
Null As DateOfServiceAfter
FROM         dbo.tblLocTransaction LT INNER JOIN
dbo.tblRouteLoc RL ON RL.LocationID = LT.LocationID
          INNER JOIN
Dbo.tblRouteDownload RD ON RD.RouteID = RL.RouteID
Where LT.TransDate < @start AND
RD.EmployeeID = @EmployeeID AND
(RD.RouteID = @RouteID1 OR
RD.RouteID = @RouteID2 OR
RD.RouteID = @RouteID3)
GROUP BY RL.RouteID, RL.LocationId
UNION ALL
SELECT     RL.RouteID As RouteID,
Null As DwnLdDate, RL.LocationId,
Null As GuideId, Null As ChangeoutDate,
Null As DateAdded, Null As DateRemoved,
Null As ServiceThreshold,
Null As DateOfServiceBefore,
Max(LT.TransDate) As DateOfServiceAfter
FROM         dbo.tblLocTransaction LT INNER JOIN
dbo.tblRouteLoc RL ON RL.LocationID = LT.LocationID
          INNER JOIN
Dbo.tblRouteDownload RD ON RD.RouteID = RL.RouteID
Where  (LT.TransDate between @start and (@stop+1)) AND
RD.EmployeeID = @EmployeeID AND
(RD.RouteID = @RouteID1 OR
RD.RouteID = @RouteID2 OR
RD.RouteID = @RouteID3)
GROUP BY RL.RouteID, RL.LocationId
UNION ALL
SELECT     RC.RouteID As RouteID,
Null As DwnLdDate, RC.LocationId,
Null As GuideId, Null As ChangeoutDate,
Max(RC.DateOfChange) As DateAdded, Null As DateRemoved,
Null As ServiceThreshold,
Null As DateOfServiceBefore, Null As DateOfServiceAfter
FROM  Dbo.tblRouteChange RC INNER JOIN
          dbo.tblRCAction RCA ON RCA.RCActionID = RC.RCActionID
Where RC.DateOfChange > @start AND
RCA.ActionDesc = @LocAdded AND
 (RC.RouteID = @RouteID1 OR
RC.RouteID = @RouteID2 OR
RC.RouteID = @RouteID3)
GROUP BY RC.RouteID, RC.LocationId
UNION ALL
SELECT     RC.RouteID As RouteID,
Null As DwnLdDate, RC.LocationId,
Null As GuideId, Null As ChangeoutDate,
Null As DateAdded, Max(RC.DateOfChange) As DateRemoved,
Null As ServiceThreshold,
Null As DateOfServiceBefore, Null As DateOfServiceAfter
FROM  Dbo.tblRouteChange RC INNER JOIN
          dbo.tblRCAction RCA ON RCA.RCActionID = RC.RCActionID
Where RC.DateOfChange > @start AND
RCA.ActionDesc = @LocRemoved AND
 (RC.RouteID = @RouteID1 OR
RC.RouteID = @RouteID2 OR
RC.RouteID = @RouteID3)
GROUP BY RC.RouteID, RC.LocationId

/* *****************************************
-- Organize Download dates into a temporary file
--**************************************** */
CREATE TABLE #temp_results4 ( RouteID INT, DwnLdDate DateTime)
SET NOCOUNT ON
INSERT INTO #temp_results4
SELECT     TEMP3.RouteID As RouteID,
          Min(TEMP3.DwnLdDate) As DwnLdDate
FROM #temp_results3 TEMP3
GROUP BY TEMP3.RouteID

/* *****************************************
-- Merge Route data w/count of locs which shouldn't be there (LocsX)

--**************************************** */
CREATE TABLE #temp_results7 (RouteID INT, RouteCode VarChar(20),
DwnLdDate DateTime,
TotLocations INT,
NumXLocations INT, TotXLocations INT)
SET NOCOUNT ON
INSERT INTO #temp_results7
Select RO.RouteID, RO.RouteCode,
IsNull(TEMP4.DwnLdDate, '20000101') As DwnLdDate,
IsNull(RD.RouteNumLocs,0) As TotLocations,
Null As NumXLocations, IsNull(RD.RouteNumLocsX,0) As TotXLocations
From #temp_results4 TEMP4 INNER JOIN
dbo.tblRoute RO ON RO.RouteID = TEMP4.RouteID INNER JOIN
Dbo.tblRouteDownload RD ON (RD.RouteID = TEMP4.RouteID AND
RD.DDateTime = TEMP4.DwnLdDate)
GROUP BY RO.RouteID, RO.RouteCode, TEMP4.DwnLdDate,
RD.RouteNumLocs, RD.RouteNumLocsX
UNION ALL
Select RO.RouteID, RO.RouteCode,
Null As DwnLdDate, Null As TotLocations,
Count( Distinct TEMP3.LocationID) As NumXLocations,
Null As TotXLocations
From #temp_results3 TEMP3 INNER JOIN
dbo.tblRoute RO ON RO.RouteID = TEMP3.RouteID
 INNER JOIN
#temp_results4 TEMP4 ON TEMP4.RouteID = TEMP3.RouteID
INNER JOIN
Dbo.tblRouteDownload RD ON (RD.RouteID = TEMP3.RouteID AND
RD.DDateTime = TEMP4.DwnLdDate)
Where TEMP3.DateOfServiceBefore IS NOT NULL AND
TEMP3.ServiceThreshold IS NOT NULL AND
RD.Xroute = 1 AND
(TEMP3.DateOfServiceBefore + TEMP3.ServiceThreshold) >
(@stop+1) AND
          NOT (TEMP3.DateAdded >  TEMP3.DateRemoved) AND
          (TEMP3.ChangeoutDate < @start OR
            TEMP3.ChangeoutDate > (@stop+1)) AND
          TEMP3.DateOfServiceAfter > @start
GROUP BY RO.RouteID, RO.RouteCode
 
/* *****************************************
-- Display Route and Xroute and employee data
--**************************************** */
SET NOCOUNT ON
Select E.EmpName As EmpName,
RouteCode,
Max(DwnLdDate) As DwnLdDate,
Max(TotLocations) As TotLocations,
Max(NumXLocations) As NumXLocations, Max(TotXLocations) As TotXLocations
From #temp_results7 TEMP7, dbo.tblEmployee E
Where E.EmployeeID = @EmployeeID
GROUP BY E.EmpName, RouteCode
0
Comment
Question by:DougMc249
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
ID: 11801931
Thre is alot to digest here, and some of the efficiencies will depend on the data structures etc, but here's a few pointers :

You create 3 variables called @RouteID1 , @RouteID2 , @RouteID3
You run 3 almost identical queries to select these 3, then later on the only place I think you use these is in WHERE clauses
 (RC.RouteID = @RouteID1 OR
 RC.RouteID = @RouteID2 OR
 RC.RouteID = @RouteID3)   etc

You would probably be much better of, not even having these variables, instead replacing the (A=B OR A=C OR A=D) bit with something like this

WHERE EXISTS (
          Select 1 FROM
         dbo.tblEmployee E  
         INNER JOIN  Dbo.tblRouteDownload RD2 ON RD.EmployeeID = E.EmployeeID  
         INNER JOIN dbo.tblRouteLoc RL ON RL.RouteID = RD.RouteID
         INNER JOIN dbo.tblRoute RO ON RO.RouteID = RD.RouteID
         INNER JOIN Dbo.tblLocService LS ON (LS.LocationID = RL.LocationID AND LS.EmployeeID = E.EmployeeID)
          Where E.EmployeeID = @EmployeeID
          AND RO.StatusCode > 0
          AND  LS.BeginDate between @start and (@stop+1)
          AND  Rc.RouteID = R2.RouteID
)

Secondly, are you sure about this particular part of the query - to get the Routes?
It seems a strange circular loop of joins. where you have linked E to RD, then RD to RL, and then  LS back to E again. These types of links are often questionable, but maybe in this case?

You do a big INSERT  INTO #Temp3 SELECT .......

then, you follow this up with a big
INSERT INTO #Temp 4 SELECT ..... FROM #Temp3 ......

There is no need in this case to insert data into #Temp3, you can just do the query straight

INSERT INTO #Temp4 SELECT ...... FROM (
          SELECT ...... whatever was the logic for Temp3 initially
) TEMP3
.....


Basically, though it is too difficult to work out what the logic is doing in detail to recommend differnet approaches, but it does appear a strange complex set of inserts, which may well be doable ina different way.
If you want you could post the more details of the tables - brief desc plus the columns of interest,(estimates of row counts etc) plus a verbal description of what the SP is trying to achieve.

And finally, you could benefit from running the whole thing through QueryAnalyser to see what the execution plan is fro the whole thing - you might get a feel there for where the bottle necks might be - e.g. table scans etc, which could be replaced by indexes.

Just remove the

ALTER Procedure dbo.spWeekReport2 (@WeekContainingThisDate datetime,
@FirstInitial char,
@LastName varchar(50))

and instead declare the params as regular TSQL params, then try Query->Display Estimated Execution Plan

You can also do Query->Index Tuning Wizard to see if it suggests any changes to indexes


This won't suggest any changes to the procedure, but it might hilight where the bottle necks are at least.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11802116
Please maintain your abandoned questions.  Here are listed all your open questions:
1 02/16/2004 500 How can data be made available through a...  Open Web Languages
2 06/14/2004 500 What is the best way to build a simple b...  Open Active Server Pages (ASP)
3 08/14/2004 500 How do you change query using temp table...  Open Microsoft SQL Server
4 06/21/2004 250 XML/SOAP what is the best way to use Map...  Open XML
0
 

Author Comment

by:DougMc249
ID: 11806375
Thanks I will work on the ideas proposed and get back to you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11806727
I appreciate you taking care of that one question, but I believe you may have overlooked these other two:

1 02/16/2004 500 How can data be made available through a...  Open Web Languages
2 06/14/2004 500 What is the best way to build a simple b...  Open Active Server Pages (ASP)

Let me know if you need help closing them.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now