Optimizing a stored procedure

[programmher]

I have a stored procedure that queries a table with over a million records. The table is indexed and does have a primary key. There are several necessary conditions within the procedure. The procedure takes several seconds to execute and return a result to a web form.

Is there anything else I can do to optimize the performance of this procedure to the results can be displayed faster?

My code is thus:


create procedure usp_PROCEDURENAME
@Client varchar(75) = NULL,
@salesman_lastname varchar(50) = NULL,
@ClientCity varchar(50) = NULL,
@ClientState varchar(4)= NULL,
@CSR_LastName varchar(50)= NULL
@CSR_EmpNum int= NULL

AS
Declare @SQLSTR varchar(2000)
select @sqlstr = 'SELECT * from tbl_ClientOrders WITH NOLOCK) where '

IF @Client IS NOT NULL
select @sqlstr = @sqlstr + 'Client like ''' + @Client + '%''' + ' and'
IF @salesman_Lastname IS NOT NULL
select @sqlstr = @sqlstr + ' salesman_lastname like ''' + @salesman_lastname + '%''' + ' and'
IF @ClientCity IS NOT NULL
select @sqlstr = @sqlstr + ' ClientCity like ''' + @ClientCity + '%''' + ' and'
IF @ClientState IS NOT NULL
select @sqlstr = @sqlstr + ' ClientState like ''' + @ClientState + '%''' + ' and'
IF @CSR_LastName IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_lastname like ''' + @CSR_lastname + '%'''+ ' and'
IF @CSR_EmpNum IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_EmpNum = @CSR_EmpNum''
IF right(@sqlstr,3) = 'and'
select @sqlstr = left(@sqlstr,datalength(@sqlstr)-3)

 

[grahams]

Try something like this:

for any parameter that you are going to use in a LIKE comparison, add a '%' to the string where the value is not null, then:

select [list all the required fields here rather than use * - it's faster]
from tbl_ClientOrders c
where c.Client like COALESCE(@Client, c.Client)
and c.Salesman_Lastname like COALESCE(@Salesman_Lastname, c.Salesman_Lastname)
and [etc etc...]
and c.CRS_EmpNum = COALESCE(@CRS_EmpNum, c.CRS_EmpNum)

That should improve things somewhat - you're saving SQL Server the bother of looking up what fields are in the table by specifying them, and you're not concatenating a string to then execute. The COALESCE function returns the first non-null value in a list, and therefore if your parameter is null it will simply compare the field to itself, effectively cancelling it out as a part of the WHERE clause.

Cheers,

Graham

 

[programmher]

Graham,

Thanks for the suggestion! I'll give it a try and let you know.

Thanks again!