Improving Performance on Like Operators RRS feed

  • Question

  • Hello..

    I want to know if there is a better way to write this query, the procedure accepts the @names as parameters. 

    User can enter Firstname or Lastname or Ph# Email or Tag--the query is performing really poor..! Really appreciate any help..!

    SELECT	  t.TicketID,t.Bcode, NULL AS rsId, p.FirstName, p.LastName, NULL AS RCode,NULL AS [From],NULL AS [To],  p.emailaddress AS EmailId, ph.PhoneNumber,  t.Key AS Code,t.PrintDateTime,v.Tg
    				FROM Tckt t
    				LEFT JOIN Vhcle v ON v.ID = t.VehicleID
    				LEFT JOIN [linkedserver].[Customer].[dbo].Profile p ON p.Profileid = t.CProfileID
    				LEFT JOIN [linkedserver].[Customer].[dbo].Phone ph ON ph.Profileid = p.profileid
    				WHERE t.TicketStatus = 1 AND
    					(p.FirstName = @FirstName OR @FirstName IS NULL OR p.FirstName LIKE '%' + @FirstName + '%')
    					AND	(p.LastName = @LastName OR @LastName IS NULL OR p.LastName LIKE '%' + @LastName + '%'  )
    					AND	(ph.PhoneNumber = @PhoneNumber OR @PhoneNumber IS NULL OR ph.PhoneNumber LIKE '%' + @PhoneNumber + '%') 
    					AND	(p.EmailAddress = @EmailId OR @EmailId IS NULL OR p.EmailAddress LIKE '%' + @EmailId + '%') 
    					AND	(v.Tg = @Tg OR @Tg IS NULL OR v.Tg LIKE '%' + @Tg + '%') 

    • Edited by naveej Wednesday, June 21, 2017 7:16 PM
    Wednesday, June 21, 2017 7:15 PM

All replies

  • Are you really looking for like pattern matching or are you looking for the first name in a string?

    So do you want to search on bob and get matches to bob, bobby, bobb? robob? If so you need to do like.

    If you are doing a token based search, ie

    would you expect a search on bob to match with Michael Bob Smith? If so, fulltext search is the best fit here.

    if you are doing a suffix (like  firstname +'%' ) based search ie bob matches with bob, bobs, bobb, bobby an index on the column will be most efficient.

    Wednesday, June 21, 2017 7:30 PM
  • This is primarily used to search the database using sp from front end, so like operator is what is needed, as there is a chance of just typing part of the characters.

    And also i have Linked server connection to get customer phone and Profile, will trying creating a view combining these two and creating an index on view. Not sure if this approach fetches some performance. Do you have any suggestions..?

    Wednesday, June 21, 2017 7:36 PM
  • Which version of SQL Server are you using?

    First be aware that you are using OUTER joins but filtering in the WHERE on the side not to preserve, changing the outer into an inner join.

    Second, not sure if you are also referencing a linked server based on the identifiers being used. In that case be sure that the poor performance is related to the use of the LIKE operator and not to the data movement.

    If you are ok with the time needed to compile the statement then you could use OPTION (RECOMPILE) and get a fresh execution plan based on the values used for the parameters on each execution (parameter sniffing).

    Check this article related to dynamic search in T-SQL. It is a little bit long but worthy of reading.

    Dynamic Search Conditions in T-SQL


    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Wednesday, June 21, 2017 7:37 PM
  • You could try, first pulling off the records from the Customer tables that match the WHERE LIKE clause into temporary tables. Then joining on the temporary tables in your query (and obviously drop the WHERE clause).

    Are the customer tables on a SQL server? Is the response time of the linked server good? How many records in each of the table?

    Have you executed the query with Actual Execution Plan? Where is most the time spent?

    Thursday, June 22, 2017 2:24 PM