SQL Server Performance

Store procedure tuning help

Discussion in 'Performance Tuning for DBAs' started by korzon, Jan 19, 2004.

  1. korzon New Member

    I have a stored procedure that for some reason takes 24 seconds to complete.
    I called it using the following code in query analyzer:

    exec Spd_SearchAssignShort '01/01/1980','01/01/2030',97005,0,0

    And here is the stored procedure itself:
    ************ beging paste ****************
    CREATE PROCEDURE Spd_SearchAssignShort
    @FromDate varchar(100),
    @ToDate varchar(100),
    @Visa Int,
    @VisaType int,
    @Driverid int

    select Assigns.AssignId,convert(datetime,left(Assigns.FromDate,11)+' '+Assigns.FromTime) FromDate,
    convert(datetime,left(Assigns.ToDate,11)+' '+Assigns.ToTime) ToDate,isnull(Drivers.DriverName,'-') Driver,isnull(Orders.Visa,1) Visa,isnull(Assigns.Route,'') Route
    from Assigns
    left join Drivers on Drivers.DriverId=Assigns.DriverId
    left join Orders on Orders.OrderId=Assigns.OrderId
    left join AssignTypes on AssignTypes.AssignTypeId=Assigns.AssignTypeId
    Left Join ActualAssigns On Assigns.AssignId = ActualAssigns.AssignId
    Not (@ToDate < Assigns.FromDate or @FromDate > Assigns.ToDate ) and
    (@Visa = Visa or @Visa is null)
    and AssignTypes.InActual=1
    and (Orders.VisaType = @VisaType or @VisaType=0)
    and (Assigns.Driverid = @DriverId or @Driverid =0)
    and ActualAssigns.AssignId is null
    ************** end paste ***************

    Here are the table sizes:
    Assigns: 72,000 rows
    Drivers: 271 rows
    Orders: 42,000 rows
    ActualAsigns: 13,000 rows
    Asign types: 10 rows

    The server configuratoin:

    OS: windows 2,000 server
    SQL SERVER: MS SQL 2,000 SP3
    CPUs: Two 700MHZ processors
    RAM: 512MB
    HD: 3 Physical drives in a RAID 5 array for DATA. 2 Physical drives in a RAID 1 array for system.

    Any thoughts?
  2. bambola New Member

    What indexes do you have on these tables? what does the execution plan show? did you try to update statistics? Are you sure you need all those left joins? how many rows corrispond to Not (@ToDate < Assigns.FromDate or @FromDate > Assigns.ToDate )
    did you try to reverse this condition not to use NOT?
  3. korzon New Member

    I do belive all appropriate indexes exist.

    The execution plan shows that the majority of the work is in the joins.

    Hash Match/leftouter join
    Argument: HASH: Assigns.OrderID = Orders.orderID
    Cost: 21%
    Hash Match/Intter join
    Argument: HASH: Orders.orderID = Orders.orderID
    COST: 40%

    I do believe all joins are neccessary.
  4. Luis Martin Moderator

    Did you run Index Tuning Wizard to find out if there is new index to implementate?

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  5. joechang New Member

    from what i can tell, you are using the stored procedure to either return records for a single Driverid or all Driverid's based on the value of @Driverid

    whatever you may have learned about code-reuse from other programming disciplines, forget it as far as SQL is concern.
    In SQL, you only describe what you want, not how to get it. Hence every query/sproc depends on the execution plan, where SQL Server figures out how to get the data you want.
    This means when you write a procedure that can either process a few records or many records, SQL Server may get confused and generate the plan best for many records, when in fact you want one plan for the few rows case, and another plan for the many rows plan

    your WHERE clause:
    and (Assigns.Driverid = @DriverId or @Driverid =0)

    SQL Server thinks you may need to scan the Assigns table, which leads to scans on the other tables as well
  6. korzon New Member

    Well, looks like you've nailed it joechang.
    Dismissing said where clause brings a 75% performance increase bringing the execution time down to five seconds. Not exactly lightspeed but acceptable.

    If I understand you correctly, I don't need to rewrite the application code. I can simply re write the SP code to use two different WHERE clauses based on whether or not @Driverid is 0.

  7. korzon New Member

    Strangely, all I did was change the order of:<br /><br />and (Assigns.Driverid = @DriverId or @Driverid =0)<br /><br />To:<br />and (@Driverid = 0 or Assigns.Driverid = @DriverId)<br /><br />And that took care of the problem. Now if the @Driverid is 0 the query takes ~5 seconds instead of ~20<br /><br />Works for me <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Thanks a lot for the help.
  8. ChrisFretwell New Member

    One thing to note, you use left joins then explicity compare against values in these tables. If you do this, it will process only matching rows unless you add or is null to these fields.

    For example AssignTypes.InActual=1
    will only return where there is a value, and the value is 1 so your join to assigntypes can be rewritten as an inner join or your where needs to be changed to (AssignTypes.InActual=1 or AssignTypes.InActual is null) etc.....I know this is a small table, but its more of a design and practice thing. Dont use outer joins when you dont need to.

    And on the subject of left joins, your join to the AcutalAssigns table appears to be used to test existance. SQL can often do this faster using an exists rather than a join. Sometimes it will use the same query plan, but sometimes its more effecient. Its worth it to try both approaches and check the query plans to see. If they are identical query plans, I personally prefer to use the if exists over the join. It makes it far more obvious as to what you are trying to accomplish without reading through a stack of join and where statements, but thats a personal choice.

  9. bhushanvinay New Member

    Can you try some thing realy bizar.
    Strip out yoru or conditions
    and make them like

    Select A,B,C from XYZ where A= @a and B=@b and c=@C
    union all
    Select A,B,C from XYZ where A= 0 and B=0 and c=0
    if i am not wrong logically this should be ok with you.

  10. bhushanvinay New Member

    and also should do a constant scan and so you should be ok with the time consumption remember to have the where caluse order also in the same order as your table primary key.
    for eg table has (a,b) as primary key then its a good practice to have the where caluse with
    where a=?
    and b=?
    rather than
    and a=?

Share This Page