Store procedure tuning help

Hi,
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
AS


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
where
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
GO
************** 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?

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?

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.

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

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.

Correct?

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.

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.

Chris

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.

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
b=?
and a=?
[]