SQL Server Performance

Looking for performance improvement in query

Discussion in 'ALL SQL SERVER QUESTIONS' started by kworden, Dec 6, 2012.

  1. kworden New Member

    I have a query which looks up route numbers from an orders table mostly based on the status of the order and the dispatch date though with a few other conditions as well. The date has to be a <= condition and the status can be one of about 10 values.

    There's about a million records in the table and the query takes around 4 seconds - I'm looking for something sub-second. There's a number of indexes on the table but the query ends up doing a clustered index seek and pretty much reads every record. I've tried putting an index on the date but it didn't use that unless I forced it, there's already an index on the status values but again it doesn't use it.

    I presume because of my query conditions that the optimizer can't find anything suitable so a scan has to be done. As the time is only going to get worse as more orders are added to the table, are there any suggestions or techniques for handling this?

    Here's the query -

    select distinct r.Route_Number,r.Description,r.Plan_Route_Date,v.Registration,ds.status,
    ds.driver_name
    from Link.Routes r
    left join Link.Vehicles v on r.Planned_Vehicle_Id=v.id
    left join Link.Driver_Sheets ds on r.route_number=ds.route and ds.status<>'CLOSED' and
    (r.driver_sheet_option is null or r.driver_sheet_option<>'UP')
    where (r.Driver_Sheet_Option is null or r.Driver_Sheet_Option<>'UP') and (ds.status is null or
    ds.status<>'CLOSED' or r.Driver_Sheet_Option<>'UP')
    -- this is the bit that takes the time
    and r.Route_Number in (select route_code from Link.Orders
    where (
    ((Status in (
    'ENTERED','AW.CONFIRMATION','AW.DATE APPROVAL','AW.PRICE CONFIRM',
    'AWAITING VALIDATION','AW.CR.CLEARANCE','HELD IN CR.CONTROL','AW.PART.SHIPMENT',
    'AW.DISPATCH CONFIRM', 'REL.TO WAREHOUSE') or
    (Status_Prior_To_Locked is not null and Status = 'LOCKED' and Status_Prior_To_Locked in (
    'ENTERED','AW.CONFIRMATION','AW.DATE APPROVAL','AW.PRICE CONFIRM',
    'AWAITING VALIDATION','AW.CR.CLEARANCE','HELD IN CR.CONTROL','AW.PART.SHIPMENT',
    'AW.DISPATCH CONFIRM', 'REL.TO WAREHOUSE'))) and Priority<=7)
    or status in ('CUT','REDELIVERY','REDELIVERY EXPECTED'))
    and order_type<>'S' and Dispatch_Date<='07-Dec-2012' and (Category_Id is null or Category_Id<>'DRCT'))
    order by r.Description
  2. Shehap MVP, MCTS, MCITP SQL Server

    I do think IN operator can be replaced better by inner join as follow:

    Create table #temp(route_code varchar (50) primary key clustered )
    insert into #temp select route_code from Link.Orders
    where
    (
    ((Status in(
    'ENTERED','AW.CONFIRMATION','AW.DATE APPROVAL','AW.PRICE CONFIRM',
    'AWAITING VALIDATION','AW.CR.CLEARANCE','HELD IN CR.CONTROL','AW.PART.SHIPMENT',
    'AW.DISPATCH CONFIRM', 'REL.TO WAREHOUSE') or
    (Status_Prior_To_Locked is not null and Status = 'LOCKED' and Status_Prior_To_Locked in(
    'ENTERED','AW.CONFIRMATION','AW.DATE APPROVAL','AW.PRICE CONFIRM',
    'AWAITING VALIDATION','AW.CR.CLEARANCE','HELD IN CR.CONTROL','AW.PART.SHIPMENT',
    'AW.DISPATCH CONFIRM', 'REL.TO WAREHOUSE'))) and Priority<=7)
    or status in('CUT','REDELIVERY','REDELIVERY EXPECTED'))
    and order_type<>'S' and Dispatch_Date<='07-Dec-2012' and(Category_Id is null or Category_Id<>'DRCT'

    )

    select distinct r.Route_Number,r.Description,r.Plan_Route_Date,v.Registration,ds.status,
    ds.driver_name
    from Link.Routes r
    inner join #temp on r.route_code= #temp.route_code
    left join Link.Vehicles v on r.Planned_Vehicle_Id=v.id
    left join Link.Driver_Sheets ds on r.route_number=ds.route and ds.status<>'CLOSED' and
    (r.driver_sheet_option is null or r.driver_sheet_option<>'UP')
    where (r.Driver_Sheet_Option is null or r.Driver_Sheet_Option<>'UP')
    and(ds.status is null or
    ds.status<>'CLOSED' or r.Driver_Sheet_Option<>'UP')
    -- this is the bit that takes the time

    order by r.Description

    Kindly try it and let me know if it helps you to be able to come up with the appropriate Index Tuning accordingly
  3. kworden New Member

    No this doesn't run any faster - it still has to run an index scan on the whole orders table to find the right records. If anything, this runs slightly slower.
  4. Shehap MVP, MCTS, MCITP SQL Server

    So we should turn into index tuning phase , so you can try the below indexes along with both T-SQL queries (The original one and that one sent above) and let me know about the results of each one

    createnonclusteredindexLink.Orders_IX1onLink.Orders(status,Status_Prior_To_Locked,order_type,Dispatch_Date,Priority,
    Category_Id)include (route_code)with (data_compression=page,fillfactor=80)

    CreatenonclusteredindexLink.Routes_IX1onLink.Routes(route_code,driver_sheet_option,Planned_Vehicle_Id,Route_Number,)
    include (Description,Plan_Route_Date)with (data_compression=page,fillfactor=80)

    CreatenonclusteredindexLink.Driver_Sheets_IX1onLink.Driver_Sheets (status,route)
    include (driver_name)with (data_compression=page,fillfactor=80)

    CreatenonclusteredindexLink.Vehicles_IX1onLink.Vehicles(id)
    include (Registration)with (data_compression=page,fillfactor=80)

Share This Page