CODEWITHSUNDEEP
sqljoinleft-join
Tom
Tom

Reputation: 12988

Slow SQL query when joining tables

This query is very very slow and i'm not sure where I'm going wrong to cause it to be so slow.

I'm guessing it's something to do with the flight_prices table
because if I remove that join it goes from 16 seconds to less than one.

    SELECT * FROM OPENQUERY(mybook,
    'SELECT  wb.booking_ref 
    FROM    web_bookings wb 
            LEFT JOIN prod_info pi ON wb.location = pi.location 
            LEFT JOIN flight_prices fp ON fp.dest_date = pi.dest_airport + '' '' + wb.sort_date
    WHERE   fp.dest_cheapest = ''Y'' 
            AND wb.inc_flights = ''Y'' 
            AND wb.customer = ''12345'' ')

Any ideas how I can speed up this join??

Upvotes: 3

Views: 12966

Answers (3)

JamieDainton
JamieDainton

Reputation: 165

You're unlikely to get any indexing on flight_prices.dest_date to be used as you're not actually joining to another column which makes it hard for the optimiser.

If you can change the schema I'd make it so flight_prices.dest_date was split into two columns dest_airport and dest_Date as it appears to be currently a composite of airport and date. If you did that you could then join like this

fp.dest_date = wb.sort_date and fp.dest_airport = pi.dest_airport

Upvotes: 4

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

Your statement reformatted gives me this

SELECT  wb.booking_ref 
FROM    web_bookings wb 
        LEFT JOIN prod_info pi ON wb.location = pi.location 
        LEFT JOIN flight_prices fp ON fp.dest_date = pi.dest_airport + ' ' + wb.sort_date
WHERE   fp.dest_cheapest = 'Y' 
        AND wb.inc_flights = 'Y' 
        AND wb.customer = '12345'

I would make sure that following fields have indexes

  • dest_cheapest
  • dest_date
  • location
  • customer, inc_flights, booking_ref (covering index)

Upvotes: 3

duffymo
duffymo

Reputation: 308743

Try EXPLAIN PLAN and see what your database comes back with.

If you see TABLE SCAN, you might need to add indexes.

That second JOIN looks rather odd to me. I'd wonder if that could be rewritten.

Upvotes: 3

Related Questions