SQL Server Performance

Query optimization is much worse in 2005

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by tmast75, Sep 11, 2006.

  1. tmast75 New Member

    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?
  2. Chappy New Member

    First thing to check: does the estimated execution plan match the actual one ?
  3. tmast75 New Member

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

    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?
  5. Adriaan New Member

    ... 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())

  6. tmast75 New Member

    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.
  7. Adriaan New Member

    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?
  8. tmast75 New Member

    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.

Share This Page