Query optimization is much worse in 2005

I have the following query :

SELECT aTable2.equip_init,aTable2.equip_nbr,aTable2.event_date, aTable2.flt_cust_id, aTable2.sub_fleet_id, aTable2.service_code, rd.clm_event_cd, rd.tr80_event_desc,
rd.le_status, rd.event_rptg_road, rd.event_splc,rd.event_city, rd.event_state, rd.origin_city, rd.origin_state
FROM (SELECT MAX(rda.event_date) AS event_date,
rda.equip_init, rda.equip_nbr,
fed.flt_cust_id,
fed.sub_fleet_id,
fed.service_code
FROM dbo.fleets_equip_list_dtl fed (NOLOCK)
INNER JOIN dbo.response_dtl rda ON
rda.equip_init = fed.equip_init
AND rda.equip_nbr = fed.equip_nbr
AND rda.event_date BETWEEN fed.start_date AND fed.end_date
WHERE fed.flt_cust_id = 'AC1'
AND fed.sub_fleet_id = 'MOTHER'
AND DateDiff(minute, end_date, GetDate ()) <= 10080
GROUP BY rda.equip_init,
rda.equip_nbr,
fed.flt_cust_id,
fed.sub_fleet_id,
fed.service_code) as aTable2
INNER JOIN response_dtl rd ON
atable2.equip_nbr = rd.equip_nbr AND
atable2.equip_init = rd.equip_init AND
atable2.event_date = rd.event_date


This query ran great under 2000, but not 2005. I have updated statistics with fullscan in 2005, and rebuilt the indexes as well. There is an index over the response_dtl table with equip_nbr, equip_init and event_date in it.

The estimated execution plan in 2005 includes a table scan over the response_dtl table, which is 37 million rows. SQL 2000 used the appropriate index and returned data quickly. Without specifying the index, 2005 will not select the correct index.

Any ideas?

First thing to check: does the estimated execution plan match the actual one ?

Yes the estimated plan does match the actual one. Even though an index exists that exactly matches, 2005 will not select it.

Replace the expression DateDiff(minute, end_date, GetDate ()) <= 10080 with an expression that does not do a calculation on the column:

SELECT ........
WHERE .......
AND end_date <= DATEADD(MINUTE, 10080, GETDATE())

Not sure if you need 10080 or -10080 ...

Do you have an index on end_date?

... uhm, the criteria should probably be something like:

AND end_date BETWEEN DATEADD(MINUTE, -10080, GETDATE()) AND GETDATE()

or

AND end_date BETWEEN GETDATE() AND DATEADD(MINUTE, 10080, GETDATE())

I tried that before and it didn't help. The inner SQL :

SELECT MAX(rda.event_date) AS event_date,
rda.equip_init, rda.equip_nbr,
fed.flt_cust_id,
fed.sub_fleet_id,
fed.service_code
FROM dbo.fleets_equip_list_dtl fed (NOLOCK)
INNER JOIN dbo.response_dtl rda ON
rda.equip_init = fed.equip_init
AND rda.equip_nbr = fed.equip_nbr
AND rda.event_date BETWEEN fed.start_date AND fed.end_date
WHERE fed.flt_cust_id = 'AC1'
AND fed.sub_fleet_id = 'MOTHER'
AND DateDiff(minute, end_date, GetDate ()) <= 10080
GROUP BY rda.equip_init,
rda.equip_nbr,
fed.flt_cust_id,
fed.sub_fleet_id,
fed.service_code

does automatically pick an appropriate index and performs well. It is only when you join this derived table with the secondary table that a problem arises.

You mentioned "There is an index over the response_dtl table with equip_nbr, equip_init and event_date in it." You don't say if there are more columns in the index besides those three, and in which order those columns are within the index.

Is the data roughly the same between the 2000 and 2005 databases?

The index contains equip_nbr, equip_init, event_date and two other unrelated columns, in that order.

The data is identical between 2000 and 2005, as we just did the conversion a few weeks ago.