INSERT SELECT takes a long time to run

Dev Shed Forums Sponsor:

February 7th, 2013, 08:11 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,598

Time spent in forums: 2 Weeks 4 Days 21 h 29 m 3 sec
Reputation Power: 70

INSERT SELECT takes a long time to run

Hello;

I was wondering if I can somehow optimise this query. It takes such a long time to execute. So long that the browser times out!
EXPLAIN
Thank you.

Code:

 INSERT INTO allotments_new
            (package_id,
             room_id,
             hotel_id,
             date_added,
             day_date,
             day_rate,
             number_of_rooms,
             status,
             supplier_id,
             day_of_the_week)
SELECT packages.id,
       packages.room_id,
       packages.hotel_id,
       Curdate(),
       annual_calendar.day_date,
       low,
       '0',
       'b',
       hotels.beta_supplier,
       Dayname(annual_calendar.day_date)
FROM   packages
       INNER JOIN hotels
               ON hotels.hotel_id = packages.hotel_id
                  AND hotels.status = 'active'
       INNER JOIN hotelrooms
               ON hotelrooms.room_id = packages.room_id
                  AND hotelrooms.status = 'active'
       LEFT OUTER JOIN calendars_new
                    ON calendars_new.hotel_id = hotels.hotel_id
                       AND calendars_new.day_date BETWEEN
                           '2013-02-08' AND '2014-02-08'
       LEFT OUTER JOIN allotments_new
                    ON allotments_new.package_id = packages.id
                       AND allotments_new.day_date BETWEEN
                           '2013-02-08' AND '2014-02-08'
                       AND calendars_new.day_date = allotments_new.day_date
       LEFT OUTER JOIN annual_calendar
                    ON annual_calendar.day_date = calendars_new.day_date
                       AND annual_calendar.day_date BETWEEN
                           '2013-02-08' AND '2014-02-08'
WHERE  packages.status = 'active'
       AND packages.hotel_id > 1800
       AND allotments_new.day_date IS NULL

Last edited by zxcvbnm : February 7th, 2013 at 09:13 PM.

February 8th, 2013, 03:46 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,370

Time spent in forums: 3 Months 1 Week 2 Days 6 h 21 m 40 sec
Reputation Power: 4140

for calendars_new and allotments_new, create composite indexes on (hotel_id,day_date) and (package_id,day_date) respectively

but i'm only guessing as i'm not a performance expert

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

February 10th, 2013, 07:04 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,598

Time spent in forums: 2 Weeks 4 Days 21 h 29 m 3 sec
Reputation Power: 70

Quote:

Originally Posted by r937

for calendars_new and allotments_new, create composite indexes on (hotel_id,day_date) and (package_id,day_date) respectively

but i'm only guessing as i'm not a performance expert

OH!!!!!It took 5 seconds!!!

Rudy