title: Optimizing SQL SP, avoid temp tables
body: <p>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.</p>
  
  <p>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! :)</p>
  
  <pre><code>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 &lt;&gt; 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
  </code></pre>