Slow SQL query when joining tables

Question

This query is very slow, and I'm not sure where I am going to make it so slow.

I assume this is related to the Flight_prices table
because if I delete this connection, it will go 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 connection?

Answer 1

You are unlikely to get indexing on flight_prices.dest_date, since you are not actually joining another column, which makes optimization difficult.

, , flight_prices.dest_date dest_airport dest_Date, , -, . ,

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

Answer 2

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'

,

• dest_cheapest
• dest_date
• , inc_flights, booking_ref ( )

Answer 3

EXPLAIN PLAN , .

TABLE SCAN, .

JOIN . , .