Jump to content

Archived

This topic is now archived and is closed to further replies.

kickstart

Slow query

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

Share this post


Link to post
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  ;)

Share this post


Link to post
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

 

 

Share this post


Link to post
Share on other sites

What happens in that last query if you change the WHERE to EXCEPT WHERE Rep.Disabled = 1 ?

 

any different?

Share this post


Link to post
Share on other sites

Hi

 

EXCEPT not supported.

 

I would also expect it to hammer performance even more to be honest

 

All the best

 

Keith

Share this post


Link to post
Share on other sites

Hi

 

Had more of a play and it seems to be an indexing issue.

 

All the best

 

Keith

Share this post


Link to post
Share on other sites

×
×
  • 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.