sql like operator %string% taking too much time on very few records

Question

I have a small database with 7 tables with 6 reference table

I need to do search on the base of key word.

So I wrote a procedure as follows:

ALTER PROCEDURE [dbo].[GetByKeyWord] @Keyword NVARCHAR(60) = '',
                                              @PageNumber BIGINT = 1,
                                                                   @PageSize BIGINT = 100 AS BEGIN
SET nocount ON;

DECLARE @Query NVARCHAR(2000) = '';
DECLARE @q1 NVARCHAR(100) = '';
DECLARE @q2 NVARCHAR(500) = '';
DECLARE @pgsize NVARCHAR(40) = '';

SET @Query ='; WITH CTE AS
( Select  [dbo].[CTable].CaseId ,[dbo].[CTable].LoanAmount,[dbo].[CTable].CommDate,[dbo].[CTable].LastSubmissionDate,[dbo].[CTable].Aging,[dbo].[CTable].BankersORCreditorsCity,[dbo].[CTable].BusinessNature,[dbo].[CT].CreditorName,
    [dbo].[DT].DebtorName,[dbo].[IT].IPName,[dbo].[ST].SectorName,[dbo].[AT].CatgoryName,[dbo].[AT].CategoryStart   , ROW_NUMBER() OVER (ORDER BY  [dbo].[CTable].DebtorId ) as rowNo
from [dbo].[CTable]  
LEFT JOIN [dbo].[DT] ON [dbo].[DT].DebtorId = [dbo].[CTable].DebtorId     
LEFT JOIN [dbo].[RT] ON [dbo].[RT].RPId = [dbo].[CTable].RPId  
LEFT JOIN  [dbo].[ST] ON [dbo].[ST].SectorId  = [dbo].[CTable].SectorId
LEFT JOIN  [dbo].[IT] ON [dbo].[IT].IPId = [dbo].[CTable].IpId
LEFT JOIN  [dbo].[AT] ON [dbo].[AT].CategoryId = [dbo].[CTable].AgingCategoryId
LEft JOIN [dbo].[CT] ON [dbo].[CT].CreditorId = [dbo].[CTable].CreditorId   
Where ( [dbo].[CTable].IsDeleted IS NULL OR [dbo].[CTable].IsDeleted = 0)   ' 

IF (@Keyword != ''
AND @Keyword IS NOT NULL) BEGIN
SELECT @q1 = TRIM(@Keyword) ;

SELECT @q2 = @q2 +' AND( [dbo].[CTable].BusinessNature LIKE '''+ '%'+@q1 +'%'+ '''' ;

SELECT @q2 = @q2 +' OR  [dbo].[DT].DebtorName LIKE '''+ '%'+@q1 +'%'+ '''' ;

SELECT @q2 = @q2 +' OR  [dbo].[CT].CreditorName LIKE '''+ '%'+@q1 +'%'+ '''' ;

SELECT @q2 = @q2 +' OR  LoanAmount LIKE '''+ '%'+@q1 +'%'+ '''' ;

SELECT @q2 = @q2 +' OR  [dbo].[ST].SectorName LIKE '''+ '%'+@q1 +'%'+ '''' ;

SELECT @q2 = @q2 +'  OR  [dbo].[IT].IPName LIKE '''+ '%'+@q1 +'%'+ ''')' ;

END
SELECT @q1 = @PageNumber ;

SELECT @pgsize = @PageSize ;

--   select @q2 = @q2 +' And [dbo].[CTable].LastSubmissionDate = '+@q1 ;

SELECT @q2 = @q2 +' )
SELECT *
FROM CTE 
WHERE    rowNo > (' + @pgsize + ' * (' + @q1 + ' - 1 ) ) 
             AND  rowNo <= ( ' + @pgsize +' * '+ @q1 + ') '
SET @Query = @Query + @q2 --select @Query
 EXEC (@Query) ;

END

In the CTable there are just 974 records still its taking around 25 seconds in query execution

What can be done to improve the performance in query execution?

What are the alternates available?

First of all, why you're using a dynamic SQL in the first place? 2nd do you have any indexes? — Ilyes, Sep 17 '19 at 09:35
Gah! Before anything fix that huge injection issue you have! Also, why is `@Keyword` passed as an `nvarchar(60)`, and the cast up to an `nvarchar(100)`? [Dos and Don'ts of Dynamic SQL](https://www.sqlservercentral.com/articles/dos-and-donts-of-dynamic-sql) — Thom A, Sep 17 '19 at 09:36
Also, the reason it's slow it's because you have leading wild cards on the search in 6 different columns; this is going to be a very expensive tasks as SQL server is going to need to check through every value, and scan the entire contents of every value. How, by the way, do you even check a value is "like" `LoanAmount`? Surely `OR LoanAmount LIKE '%John Smith%'` is going to give you a conversion error as `LoanAmount` will be a numerical data type. — Thom A, Sep 17 '19 at 09:39
Another note, putting the name of the schema prior to a column name is deprecated and should be avoided. Just use the format `[{Object Name/Alias}].[{Column Name}]` — Thom A, Sep 17 '19 at 09:41
so i should not use dynamic sql, and @keyword should of smaller size — Mohd Maaz, Sep 17 '19 at 09:43
There's no need for Dynamic SQL here, no; there's nothing dynamic in it (I discuss that in the linked above article). And `@Keyword` should be an appropriate size but you're simply putting the value into `@q1`, which is a larger size; which makes no sense. — Thom A, Sep 17 '19 at 09:45
If it's not giving an error, is `LoanAmount` not a numerical data type? If so, that's a problem unto itself as well, @MohdMaaz (unless the name does not describe what it actually is, as a column with the name `LoanAmount` strongly implies it'll contain a **numerical** value of the amount that was loaned (i.e. `10000`)). — Thom A, Sep 17 '19 at 09:45
no @Larnu its of decimal type and its working even when i search a loan amount ,may be it is getting casted by default — Mohd Maaz, Sep 17 '19 at 09:49
@Larnu - `LIKE` is a special case, since it only works with strings. Example: `SELECT 1 WHERE CAST( 1 AS INT ) LIKE '%abc%'` - this works, and this fails as you would expect: `SELECT 1 WHERE CAST( 1 AS INT ) = '%abc%'` — Alex, Sep 17 '19 at 10:00
Do you really need to search every column here? A value like IP Address is going to be significantly different to a Loan Amount (which I doubt you need a `LIKE` for), which is going to be different again to a Name. Sounds like you should have separate parameters for each, and only search those that are supplied. — Thom A, Sep 17 '19 at 10:02
What are the relationships between `CTable` and all others? Are they all 1 to (0, 1) or are they 1 to (0, many)? — Alex, Sep 17 '19 at 10:23

score 1 · Answer 1 · answered Sep 17 '19 at 11:58

Please check below point for optimization.
1. Use UNION ALL instead of OR for little bit faster.
2. Break your code in small small part
3. Use table alias instead of full qualified name in column selection. So it will take less characters in dynamic query.
4. Please use column name instead of * in CTE column selection also.
5. Please use sp_executesql SP for dynamic query. Check this link for more knowledge.

Please check below updated stored procedure code.

ALTER PROCEDURE [dbo].[GetByKeyWord] 
    @Keyword NVARCHAR(60) = '',
    @PageNumber BIGINT = 1,
    @PageSize BIGINT = 100 
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE 
        @Query NVARCHAR(2000) = '',
        @QRY NVARCHAR(700) = '',
        @q1 NVARCHAR(100) = '',
        @q2 NVARCHAR(1500) = '',
        @pgsize NVARCHAR(40) = ''

    SELECT 
        @q1 = RTRIM(LTRIM(@Keyword)),
        @Keyword = NULLIF(@Keyword,'')

    SET @Query = '
    ;WITH CTE 
    AS
    ( '

    -- Please use table alias so it will take less characters in dynamic query
    SET @QRY = '
    SELECT  
        CT.CaseId ,
        CT.LoanAmount,
        CT.CommDate,
        CT.LastSubmissionDate,
        CT.Aging,
        CT.BankersORCreditorsCity,
        CT.BusinessNature,
        C.CreditorName,
        D.DebtorName,
        I.IPName,
        S.SectorName,
        A.CatgoryName,
        A.CategoryStart, 
        ROW_NUMBER() OVER (ORDER BY CT.DebtorId) as rowNo
    FROM [dbo].[CTable] AS CT 
    LEFT JOIN [dbo].[DT] AS D ON D.DebtorId = CT.DebtorId 
    LEFT JOIN [dbo].[RT] AS R ON R.RPId = CT.RPId  
    LEFT JOIN [dbo].[ST] AS S ON S.SectorId  = CT.SectorId
    LEFT JOIN [dbo].[IT] AS I ON I.IPId = CT.IpId
    LEFT JOIN [dbo].[AT] AS A ON A.CategoryId = CT.AgingCategoryId
    LEft JOIN [dbo].[CT] AS C ON C.CreditorId = CT.CreditorId   
    Where (CT.IsDeleted IS NULL OR CT.IsDeleted = 0) '

    IF (@Keyword != '' AND @Keyword IS NOT NULL) 
    BEGIN

        SELECT @q2 = @QRY + ' AND CT.BusinessNature LIKE ''' + '%' + @q1 + '%' + '''' ;

        SELECT @q2 = @q2 + ' UNION ALL ' + @QRY + ' AND D.DebtorName LIKE ''' + '%' + @q1 + '%' + '''' ;

        SELECT @q2 = @q2 + ' UNION ALL ' + @QRY + ' AND C.CreditorName LIKE ''' + '%' + @q1 + '%' + '''' ;

        SELECT @q2 = @q2 + ' UNION ALL ' + @QRY + ' AND CT.LoanAmount LIKE ''' + '%' + @q1 + '%' + '''' ;

        SELECT @q2 = @q2 + ' UNION ALL ' + @QRY + ' AND S.SectorName LIKE ''' + '%' + @q1 + '%' + '''' ;

        SELECT @q2 = @q2 + ' UNION ALL ' + @QRY + ' AND I.IPName LIKE ''' + '%' + @q1 + '%' + '''' ;

    END

    SELECT @q1 = @PageNumber;

    SELECT @pgsize = @PageSize;

    --select @q2 = @q2 +' And [dbo].[CTable].LastSubmissionDate = '+@q1 ;

    -- Use this condition for add select query to q2 if keyword is null
    IF @Keyword IS NULL
    BEGIN

        SELECT @q2 = @q2 + @QRY

    END

    --Please use column names instead of *
    SELECT @q2 = @q2 + ' )
    SELECT 
        C.CaseId ,
        C.LoanAmount,
        C.CommDate,
        C.LastSubmissionDate,
        C.Aging,
        C.BankersORCreditorsCity,
        C.BusinessNature,
        C.CreditorName,
        C.DebtorName,
        C.IPName,
        C.SectorName,
        C.CatgoryName,
        C.CategoryStart, 
        C.rowNo
    FROM CTE AS C
    WHERE C.rowNo > (' + @pgsize + ' * (' + @q1 + ' - 1 ) ) 
    AND C.rowNo <= ( ' + @pgsize + ' * ' + @q1 + ') '

    SET @Query = @Query + @q2 --select @Query

    -- Please use sp_executesql SP for dynamic query
    EXECUTE sp_executesql @Query

    --EXEC (@Query) ;

END

score 0 · Answer 2 · answered Sep 17 '19 at 10:27

There are two main performance problems with your query:

The use of LIKE '%%', which will be slow, as it will require a table / index scan for each table referenced.
The use of OR in the WHERE clause.

Solutions:

For LIKE '%%' type of searches I would recommend that you setup a FULLTEXT INDEX. It is not hard to setup, especially for only a few tables and it will practically eliminate performance problems with LIKE searches.

The OR condition is a little trickier as I believe you may have a subtle bug in your current query, depending on the relationships between CTable and others. (see my comment)

sql like operator %string% taking too much time on very few records

2 Answers2