Slow query

[kickstart]

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

[Muddy_Funster]

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? 

 

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 

[kickstart]

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

 

 

[Muddy_Funster]

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

 

any different?

[kickstart]

Hi

 

EXCEPT not supported.

 

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

 

All the best

 

Keith

[kickstart]

Hi

 

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

 

All the best

 

Keith