Slow SQL query when joining tables

Thi开发者_如何学Pythons 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??

---

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

---

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.

---

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)