Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Query takes about 20 sec :(

    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
    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

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    just a question..

    how does the UI look like? does it have different text boxes to enter each of the search items or its a single text box where user enters a text to be searched?
    Cheers....

    baburajv

  3. #3
    Join Date
    Sep 2011
    Posts
    31
    Few things.
    Try running the query of the SP in query window and see if the performance is same. This sp is a potential candidate for parameter sniffing issue.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    So, you are using criteria against every single field (or almost every single field) in the table . . .

    . . . what is your indexing arrangement ???
    Last edited by PracticalProgram; 10-14-11 at 12:33.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    There are a lot of search criteria in your query what you can do is use indexing feature also you can see the query execution plan for the query built to find out which task takes the most cost

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •