Query takes about 20 sec :( - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query takes about 20 sec :(

Author

Topic

adi.shoukat
Starting Member

1 Post

Posted - 2011-10-13 : 18:53:54

[code]
CREATE proc [dbo].[searchCustomer]
@searchText nvarchar(255),
@PageIndex integer,
@PageSize integer,
@sortOrder nvarchar(255),
@sortBy nvarchar(255)
as
begin

WITH page AS (
select customers.*,offices.location AS officeLocation, Employees.Name AS salesAgent, ROW_NUMBER() OVER (ORDER BY
case when @sortBy = 'CustomerName' and @sortOrder = 'ASC'
then CustomerName end asc,
case when @sortBy = 'CustomerName' and @SortOrder = 'DESC'
then CustomerName end desc,

case when @sortBy = 'address' and @SortOrder = 'DESC'
then customers.Location end desc,
case when @sortBy = 'address' and @SortOrder = 'ASC'
then customers.Location end asc,

case when @sortBy = 'phoneNo' and @SortOrder = 'DESC'
then PhoneNo end desc,
case when @sortBy = 'phoneNo' and @SortOrder = 'ASC'
then PhoneNo end asc,

case when @sortBy = 'office' and @SortOrder = 'DESC'
then offices.location end desc,
case when @sortBy = 'office' and @SortOrder = 'ASC'
then offices.location end asc,

case when @sortBy = 'status' and @SortOrder = 'DESC'
then CustomerStatusId end desc,
case when @sortBy = 'status' and @SortOrder = 'ASC'
then CustomerStatusId end asc,

case when @sortBy = 'salesAgent' and @SortOrder = 'DESC'
then Employees.Name end desc,
case when @sortBy = 'salesAgent' and @SortOrder = 'ASC'
then Employees.Name end asc

) AS Row

from customers
inner join offices on offices.officeID = customers.officeID
inner join Employees on Employees.EmployeeID = customers.SalesRepID
where
CustomerCode like '%'+@searchText+'%'
or CustomerName like '%'+@searchText+'%'
or customers.Location like '%'+@searchText+'%'
or customers.City like '%'+@searchText+'%'
or Zipcode like '%'+@searchText+'%'
or PersonMobileNo like '%'+@searchText+'%'
or PhoneNo like '%'+@searchText+'%'
or ContactPerson like '%'+@searchText+'%'
or Email like '%'+@searchText+'%'
or Website like '%'+@searchText+'%'
or CustomerDirections like '%'+@searchText+'%'
or CustomerNotes like '%'+@searchText+'%'
or Balance like '%'+@searchText+'%'
or offices.location like '%'+@searchText+'%'
or Employees.Name like '%'+@searchText+'%' )
SELECT * FROM page WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

end
[/code]

This is my stored procedure I am running it on 15,000 records. It takes 10-20 seconds to get results :(
Can anyone tell me how can i improve the performance?
Thanks

Regards,
Adil Shoukat

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-14 : 04:41:12

I suspect it is the multiple search conditions that can't use an index seek.
try just

select count(*)
from customers
inner join offices on offices.officeID = customers.officeID
inner join Employees on Employees.EmployeeID = customers.SalesRepID
where
CustomerCode like '%'+@searchText+'%'
or CustomerName like '%'+@searchText+'%'
or customers.Location like '%'+@searchText+'%'
or customers.City like '%'+@searchText+'%'
or Zipcode like '%'+@searchText+'%'
or PersonMobileNo like '%'+@searchText+'%'
or PhoneNo like '%'+@searchText+'%'
or ContactPerson like '%'+@searchText+'%'
or Email like '%'+@searchText+'%'
or Website like '%'+@searchText+'%'
or CustomerDirections like '%'+@searchText+'%'
or CustomerNotes like '%'+@searchText+'%'
or Balance like '%'+@searchText+'%'
or offices.location like '%'+@searchText+'%'
or Employees.Name like '%'+@searchText+'%' )

Could try just getting PKs to return then getting the full resultset for those PKs.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-10-14 : 05:37:28

Have you considered using FULLTEXT?
And, as a sidenote, don't post queries using WITH in a SQL Server 2000 forum.

N 56°04'39.26"
E 12°55'05.63"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-14 : 16:55:38

Another option would be to coordinate your user interface with the SP - have separate search fields for each column each with their own input parameter. Then your WHERE clause could be more like:

where (@customerCode is NULL OR CustomerCode like '%'+@customerCode+'%')
and (@CustomerName is NULL OR CustomerName like '%'+@@CustomerName+'%')
and ...

That way the only columns "searched" are the one(s) user specified with search text.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources