CODEWITHSUNDEEP
sqlsql-serversql-optimization
Jon Martin
Jon Martin

Reputation: 3390

Slow SQL query, not sure how to optimize

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.

Upvotes: 4

Views: 2605

Answers (4)

Eric Burdo
Eric Burdo

Reputation: 814

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...

Upvotes: 2

Ben
Ben

Reputation: 52923

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

Upvotes: 5

Stuart Blackler
Stuart Blackler

Reputation: 3772

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

Upvotes: 1

Marc B
Marc B

Reputation: 360922

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.

Upvotes: 12

Related Questions