Jump to content

Slow query


kickstart

Recommended Posts

Hi

 

I have the following query which runs adequately:-

 

SELECT Rep.RepID, Rep.Forname + ' ' + Rep.Surname AS Name, ROUND(dbo.Haversine(OutcodePostCodes1.lat, OutcodePostCodes1.lng, OutcodePostCodes2.lat, OutcodePostCodes2.lng), 0) AS RepDistance 
FROM Rep INNER JOIN RepQuery ON Rep.RepID = RepQuery.RepID AND RepQuery.QueryId = 7 
CROSS JOIN (SELECT * FROM OUTLETS WHERE OUTLETS.OutletID = SOMEOUTLETID ) AS OUTLETS 
LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes1 ON dbo.OutboundPostcode(OUTLETS.PostCode) = OutcodePostCodes1.Outcode 
LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes2 ON Rep.OutPostcode = OutcodePostCodes2.Outcode 
ORDER BY RepDistance

 

This is OK, but it lands up returning reps who are no longer employed. However occasionally I will want them returned if they are assigned to the current job. So I tried the following:-

 

SELECT Rep.RepID, Rep.Forname + ' ' + Rep.Surname AS Name, ROUND(dbo.Haversine(OutcodePostCodes1.lat, OutcodePostCodes1.lng, OutcodePostCodes2.lat, OutcodePostCodes2.lng), 0) AS RepDistance 
FROM Rep INNER JOIN RepQuery ON Rep.RepID = RepQuery.RepID AND RepQuery.QueryId = 7 
CROSS JOIN OUTLETS 
LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes1 ON dbo.OutboundPostcode(OUTLETS.PostCode) = OutcodePostCodes1.Outcode 
LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes2 ON Rep.OutPostcode = OutcodePostCodes2.Outcode 
WHERE OUTLETS.OutletID = SOMEOUTLETID AND Rep.Disabled = 0 "
UNION 
SELECT Rep.RepID, Rep.Forname + ' ' + Rep.Surname AS Name, 6000 AS RepDistance 
FROM Rep 
CROSS JOIN OUTLETS 
WHERE OUTLETS.OutletID = SOMEOUTLETID AND Rep.Disabled = 1 AND Rep.RepID = SOMEREP
ORDER BY RepDistance"

 

This does work, but runs unbelievably slowly.

 

Taking a step back I modified the first query to just exclude the ex reps

 

SELECT Rep.RepID, Rep.Forname + ' ' + Rep.Surname AS Name, ROUND(dbo.Haversine(OutcodePostCodes1.lat, OutcodePostCodes1.lng, OutcodePostCodes2.lat, OutcodePostCodes2.lng), 0) AS RepDistance 
FROM Rep INNER JOIN RepQuery ON Rep.RepID = RepQuery.RepID AND RepQuery.QueryId = 7 
CROSS JOIN (SELECT * FROM OUTLETS WHERE OUTLETS.OutletID = SOMEOUTLETID ) AS OUTLETS 
LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes1 ON dbo.OutboundPostcode(OUTLETS.PostCode) = OutcodePostCodes1.Outcode 
LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes2 ON Rep.OutPostcode = OutcodePostCodes2.Outcode 
WHERE Rep.Disabled = 0 
ORDER BY RepDistance

 

This also takes ages, despite the only difference being a check of a bit field (set to is indexable) to dis-guard maybe 25% of the ~25 records returned.

 

Unfortunately I have no tools for investigating SQL Server queries properly so I am a bit stumped with something that looks like a minor change (and which even if it triggered a table scan, is only scanning a couple of dozen records) which is causing dramatically worse performance.

 

All the best

 

Keith

Link to comment
Share on other sites

I know this is going to sound old hat, patronising and all the rest of it, and since you know a shed load more about this stuff than I do it's the best I got at the moment.  Have you tried replacing the * with field names in your cross join subquery?  :D

 

I know, I know, but you've help me that much in the past I wouldn't feel right if I didn't at least try  ;)

Link to comment
Share on other sites

Hi

 

The sub query is not 100% essential. The query did use a conventional join against the table and then excluded records in the normal WHERE clause, but the sub query was an attempt to sort a few records out earlier in the process.

 

In the SQL here it is the one with the sub query that is quickest but unfortunately it makes stuff all difference to performance either way :'( .

 

All the best

 

Keith

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.