Slow SQL query, not sure how to optimize

Question

So I have to deal with a database that has no indexes (not my design, it frustrates the hell out of me). I'm running a query that takes approximately three seconds to return, and I need it to be faster.

Here are the relevant tables and columns:

gs_pass_data          au_entry            ground_station
  -gs_pass_data_id      -au_id              -ground_station_id
  -start_time           -gs_pass_data_id    -ground_station_name
  -end_time             -comments
  -ground_station_id

And my query is:

SELECT DISTINCT gs_pass_data_id,start_time,end_time,
  ground_station_name FROM gs_pass_data 
  JOIN ground_station
  ON gs_pass_data.ground_station_id =
  ground_station.ground_station_id 
  JOIN au_entry ON au_entry.gs_pass_data_id =
  gs_pass_data.gs_pass_data_id
WHERE (start_time BETWEEN @prevTime AND @nextTime) 
  AND comments = 'AU is identified.'
  ORDER BY start_time

I've tried using EXISTS instead of DISTINCT with no improvements. I've read everything I can about SQL optimization but I cannot seem to get this query down to a reasonable time (reasonable being < 0.5 seconds). Any ideas would be greatly appreciated.

Answer 1

Without indexes, you're hosed. The DB engine will have to do full table scans, each time, every time. Fiddling with the queries is just rearranging deck chairs on the Titanic. Fix the DB now, before it gets even worse as data piles up.

Answer 2

The query can also be written without the distinct and with a group by instead. It'll probably make no difference at all though. Standard advice is the same as everyone else's. Add indexes, drop 'order by` so +1 to @Marc B

SELECT gs_pass_data_id,start_time,end_time,ground_station_name 
  FROM gs_pass_data 
  JOIN ground_station
    ON gs_pass_data.ground_station_id = ground_station.ground_station_id 
  JOIN au_entry 
    ON au_entry.gs_pass_data_id = gs_pass_data.gs_pass_data_id
 WHERE (start_time BETWEEN @prevTime AND @nextTime) 
   AND comments = 'AU is identified.'
 GROUP BY gs_pass_data_id,start_time,end_time,ground_station_name 
 ORDER BY start_time