Slow query when limited to a date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Slow query when limited to a date range

Author

Topic

Leigh79
Starting Member

28 Posts

Posted - 2010-02-02 : 04:32:02

I have the following query:

SELECT enq_id, enq_brand, sta_color, enq_cons_id, enq_cons_dept, enq_date, hol_datefrom, hol_dateto, hol_adults, hol_children,
hol_duration, cust_surname, enq_status
FROM crm_enquiries
INNER JOIN crm_holidays ON enq_id = hol_enq_id
INNER JOIN crm_customer ON enq_cust_id = cust_id
INNER JOIN crm_status ON enq_status = sta_id
LEFT OUTER JOIN crm_arrivals ON enq_id = arr_hol_id
LEFT OUTER JOIN crm_departures ON enq_id = dep_hol_id
LEFT OUTER JOIN crm_arrivals_cruise ON enq_id = cru_hol_id
LEFT OUTER JOIN crCruiseLine ON cru_line_id = ID
LEFT OUTER JOIN (
SELECT etr_enq_id, MAX(etr_date) AS [Date]
FROM crm_enquiries_tracking
GROUP BY etr_enq_id ) tbTracking ON enq_id = etr_enq_id
WHERE
(enq_date >= '29/01/2010') AND (enq_date <= '02/02/2010') AND (hol_type = 'CRUISE')
GROUP BY enq_id, enq_brand, sta_color, enq_cons_id, enq_cons_dept, enq_date, hol_datefrom, hol_dateto, hol_adults, hol_children,
hol_duration, cust_surname, enq_status, [Date]
ORDER BY enq_status

If I run this query without a 'Date To' it returns the results instantly, however as soon as the Date To is added it takes over a minute to return the same results.

The enq_date column is indexed to sort ASC.

Can anyone give me some help with this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:04:58

whats the purpose of group by in above query? you're not doing any aggregation here