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.

Using `EXISTS` is the only syntax optimisation available. You'll need to add some indexes! — Martin Smith, Aug 25 '11 at 16:03
You have **NO INDEXES**, and can't add any? Whose design is this and how can you go around them? — JNK, Aug 25 '11 at 16:03
I was afraid of this. Unfortunately I'm just a co-op student, and have no say in any matter about this. Very frustrating. — Jon Martin, Aug 25 '11 at 16:04
Indexes are your best method for improving speed. Dropping the ORDER BY could help some. — Lost in Alabama, Aug 25 '11 at 16:06
@Lost in Alabama: I did play around with removing the ORDER BY clause, with insignificant results :( I would have to agree with you on the indexes. — Jon Martin, Aug 25 '11 at 16:07
Good @Jon. How about archiving some records from the tables? If you're going to have to do full table scans, then reducing the number of records might help. — Lost in Alabama, Aug 25 '11 at 16:10
Updated my answer. Doubt it will help but give it a go! :) untested, fyi — Stuart Blackler, Aug 25 '11 at 16:26

score 12 · Answer 1 · answered Aug 25 '11 at 16:04

12

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.

answered Aug 25 '11 at 16:04

Marc B

356,200
43
426
500

1

Yeah I realize this. I have no control over the database, but I will try to get through to someone on this. – Jon Martin Aug 25 '11 at 16:06
2

Agreed - Indexes will be the best thing to affect your performance here. (On the plus side, at least the dataset appears small, if it only takes 3 seconds to run that query). – Clockwork-Muse Aug 25 '11 at 16:15

score 5 · Accepted Answer · answered Aug 25 '11 at 16:51

5

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

answered Aug 25 '11 at 16:51

Ben

51,770
36
127
149

If you wrap the Au is identified bit in an nvarchar instead it should make it a tiny bit faster as you are not converting implicitly. Eg. `N'Au is...'` – Stuart Blackler Aug 25 '11 at 17:02
Did this actually speed it up? It should make barely any difference at all and might even slow it down. – Ben Aug 25 '11 at 17:26
I havent had time to test it. But I have started a discussion here: http://dba.stackexchange.com/questions/5038/in-memory-performance – Stuart Blackler Aug 25 '11 at 17:34

score 2 · Answer 3 · answered Aug 25 '11 at 17:06

Since you can't create indexes on the tables... do you have the authority to created indexed views?

SQL 2005 - http://technet.microsoft.com/en-us/library/cc917715.aspx

SQL 2008 - http://msdn.microsoft.com/en-us/library/dd171921(v=sql.100).aspx

That would give you the benefit of indexes, but not alter the original tables...

score 1 · Answer 4 · answered Aug 25 '11 at 16:24

You can try the following, I don't know what else you can do or if this will make it any faster at all :/

SELECT DISTINCT gs_pass_data_id,start_time,end_time,ground_station_name 
  FROM
  (
    -- My idea is to make this first table as small as possible first, which will then make the joins quicker (TM)
    SELECT *
    FROM gs_pass_data
    WHERE (start_time BETWEEN @prevTime AND @nextTime)
  ) t
  INNER JOIN ground_station ON gs_pass_data.ground_station_id = ground_station.ground_station_id 
  INNER JOIN 
  (
    -- Same as above
    SELECT *
    FROM au_entry
    WHERE comments = N'AU is identified.' -- Make sure comments is the same type as the text string. You said nvarchar so make the string your searching by nvarchar
  ) t2  ON au_entry.gs_pass_data_id = gs_pass_data.gs_pass_data_id
ORDER BY start_time

-- OR TRY THIS

SELECT DISTINCT gs_pass_data_id,start_time,end_time,ground_station_name 
  FROM
  (
    -- My idea is to make this first table as small as possible first, which will then make the joins quicker (TM)
    SELECT *
    FROM gs_pass_data
    WHERE (start_time BETWEEN @prevTime AND @nextTime)
  ) t
  INNER JOIN ground_station ON gs_pass_data.ground_station_id = ground_station.ground_station_id 
  INNER JOIN au_entry ON au_entry.gs_pass_data_id = gs_pass_data.gs_pass_data_id
  WHERE comments = N'AU is identified.' -- Make sure comments is the same type as the text string. You said nvarchar so make the string your searching by nvarchar
ORDER BY start_time

These queries are both giving me 'multi-part identifier could not be bound' errors. — Jon Martin, Aug 25 '11 at 16:40
All should need to do is replace the select with `t.gs_pass_data_id,t.start_time,t.end_time,ground_station_name` — Stuart Blackler, Aug 25 '11 at 17:03

Slow SQL query, not sure how to optimize

4 Answers4