Optimizing SQL SP, avoid temp tables

Question

I have a SP which is performing really poorly right now. I have some indexes in the key columns, but the speed of the SP is around 14 seconds to pull out 5k rows, which I would expect to be sub-second. I assume it's related to the number of temp tables I have in there, but I'm not sure exactly how to avoid them giving I need to access the data in various places in the SP.

Although I have indexes and things set up, please do suggest anything that you think would improve performance, I'd rather be told to do something I'm already doing, rather than not be told something important! :)

CREATE PROCEDURE [dbo].[GetPlacesByLatLong]
@minLat FLOAT,
@maxLat FLOAT,
@minLong FLOAT,
@maxLong FLOAT,
@startTime BIGINT,
@endTime BIGINT
AS

SELECT Place_routes.*
INTO #tempRoutes
FROM Place_routes
WHERE latitude BETWEEN @minLat AND @maxLat
AND longitude BETWEEN @minLong AND @maxLong


SELECT Place.*, #tempRoutes.id AS route_id
INTO #tempRoute_Places
FROM #tempRoutes
INNER JOIN link_Place_routes
ON link_Place_routes.route_id = #tempRoutes.id
INNER JOIN Place ON link_Place_routes.Place_id = Place.id
WHERE latitude BETWEEN @minLat AND @maxLat
AND longitude BETWEEN @minLong AND @maxLong

SELECT * 
INTO #tempLocations 
FROM locations 
WHERE latitude BETWEEN @minLat AND @maxLat
AND longitude BETWEEN @minLong AND @maxLong

SELECT * INTO #tempPlaces FROM (
SELECT * FROM (
SELECT Place.*, 0 as route_id
FROM #tempLocations
INNER JOIN Place
ON #tempLocations.id = Place.location 
WHERE time_start BETWEEN @startTime AND @endTime
UNION 
SELECT #tempRoute_Places.* FROM #tempRoute_Places
) AS blah
UNION
SELECT * FROM (
SELECT Place.*, 0 as route_id
FROM #tempLocations
INNER JOIN Place
ON #tempLocations.id IN (SELECT #tempRoute_Places.location FROM #tempRoute_Places)
WHERE time_start BETWEEN @startTime AND @endTime
UNION 
SELECT #tempRoute_Places.* FROM #tempRoute_Places
) AS blah
WHERE time_start BETWEEN @startTime AND @endTime
OR route_id <> 0
) AS blah2

ORDER BY id


SELECT * FROM #tempRoutes

SELECT * FROM #tempPlaces
UNION
SELECT external_Place_routes.Place_id, null, null, null, null, null, null, null, null, null, null, null, null, external_Place_routes.route_id, null, null FROM external_Place_routes
SELECT * FROM #tempLocations
WHERE id IN (SELECT location FROM #tempPlaces)
SELECT * FROM Place_images 
WHERE Place_id in ( SELECT id FROM #tempPlaces )
ORDER BY Place_id

SELECT link_Place_types.Place_id,[types].*
FROM link_Place_types 
INNER JOIN [types] 
ON [types].id = link_Place_types.type_id
WHERE Place_id in ( SELECT id FROM #tempPlaces )
ORDER BY link_Place_types.Place_id

maybe try Exists instead of In. It's not much but it might help a bit. — Johnny Fitz
– Johnny Fitz, Commented Apr 30, 2014 at 0:16
1. Alias your tables and then use those aliases against columns. This does nothing for performance but assists in understanding your code and stops it crashing if you add a column of the same name to a different table. 2. Add some timing code to your SP and evaluate which statement is causing the most impact. 3. Lose the SELECT *. Always use a explicit column list. — Nick.Mc
– Nick.Mc, Commented Apr 30, 2014 at 0:34
Try adding indexes to your #temptables, like on id in #tempPlaces; +1 on the dropping SELECT *; and why is the SELECT * from #tempRoutes there, it doesn't go anywhere? — SqlACID
– SqlACID, Commented Apr 30, 2014 at 1:04
Also, in simple explanation, please explain what you are TRYING to do/get. Somewhat obvious about lat/long orientation, but what is the final task supposed to complete. Also, definitely get the query with table.column or alias.column format as nothing guarantees which table the columns are coming from... — DRapp
– DRapp, Commented Apr 30, 2014 at 1:44
Hey, I was really just hoping for advice like "don't use a temp table, you should do something like insert magic here". :) I get that there is cleanup work to do and aliasing is nice, and all the other good suggestions you guys made, but the core issue here is that there is something hindering performance. Putting an index on the temp table doesn't help as it's regenerated every call, it actually appears to slow the query down marginally. I'll do my own benchmarking, I was really just hoping you guys would be able to say "avoid blah, try doing blah instead" :-| — Faraday
– Faraday, Commented Apr 30, 2014 at 2:17

dean · Accepted Answer · 2014-04-30 06:59:34Z

2

Temporary tables in your case are probably rather helping the performance.

Three things that immediatelly pop out:

Don't use SELECT * in production code, ever. Try to narrow down the select list to just the columns you really need -- less data to shuffle around and you could even create covering indexes then.
Get rid of the ORDER BY id from the SELECT * INTO #tempPlaces statement, it serves no purpose and introduces sorting (or could affect the index choice)
Make sure that datatypes of parameters match the datatypes of columns. Otherwise, the implicit conversions could prevent the use of indexes (you do have indexes on predicate columns, don't you?).

For anything else we'll have to see the execution plan.

answered Apr 30, 2014 at 6:59

dean

10.2k2 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Faraday Over a year ago

ORDER BY id orders the results. I mean I can move it down to be done later in the SP, but it needs to be included so that the results are ordered correctly. Datatypes are all correct.

dean Over a year ago

Still, order by as part of select into is useless (no identity, no clustered index). I'd be interested to see the execution plan XML and the output of set statistics io on and set statistics time on.

Johnny Fitz · Accepted Answer · 2014-04-30 00:31:51Z

0

This might help a bit:

  SELECT * INTO #tempPlaces FROM

includes the following where in a few places.

WHERE time_start BETWEEN @startTime AND @endTime

Maybe leave it in where it appears at the end of this statement but take it out of the union statements.

SELECT * INTO #tempPlaces FROM (
SELECT * FROM (
SELECT Place.*, 0 as route_id
FROM #tempLocations
INNER JOIN Place
ON #tempLocations.id = Place.location 
UNION 
SELECT #tempRoute_Places.* FROM #tempRoute_Places
) AS blah
UNION
SELECT * FROM (
SELECT Place.*, 0 as route_id
FROM #tempLocations
INNER JOIN Place
ON #tempLocations.id IN (SELECT #tempRoute_Places.location FROM #tempRoute_Places)
UNION 
SELECT #tempRoute_Places.* FROM #tempRoute_Places
) AS blah
WHERE time_start BETWEEN @startTime AND @endTime
OR route_id <> 0
) AS blah2

answered Apr 30, 2014 at 0:31

Johnny Fitz

5422 silver badges8 bronze badges

2 Comments

Faraday Over a year ago

That degraded performance. It ran for over a minute, and didn't complete (I stopped it).

Johnny Fitz Over a year ago

Strange. Try to get rid of the select from (select from, perform separate inserts instead of select into. Basically get rid of unions. Did you try exists instead of In? Actual execution plan? Union all instead of union? Think separate inserts into #templaces would be better though.

Collectives™ on Stack Overflow

Optimizing SQL SP, avoid temp tables

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related