CODEWITHSUNDEEP
sqlsql-serversqlperformance
Daniel Veintimilla Tenorio
Daniel Veintimilla Tenorio

Reputation: 41

SQL Server query performance alternative (Concatenating strings in LIKE statemen)

I have one performance issue on a huge SQL Server table. We have this query:

    SELECT cycle
       FROM   product AS pr
       INNER JOIN productdetails AS prd
           ON prd.label = 'XRT354511' 
           AND ( pr.cycle = prd.cycle OR prd.cycle LIKE pr.cycle+ '-EXECUTED%' )

Condition after OR statement is using like to concatenate product table cycle with -EXECUTED (We don't care about the '-EXECUTED' position in cycle, we just need to know if the cycle contains -EXECUTED).

Then, my proposal to fix this issue is change that query for this one:

SELECT cycle
   FROM   product AS pr
   INNER JOIN productdetails AS prd
       ON prd.label = 'XRT354511' 
       AND ( pr.cycle = prd.cycle
          OR (pr.cycle = prd.cycle AND pr.cycle LIKE '-EXECUTED%'))

When i executed second query, it runs pretty fast and smooth.

Is my proposal valid, Can i use my proposal and get the same results than first query?

Thanks

Upvotes: 0

Views: 108

Answers (3)

etsa
etsa

Reputation: 5060

Your question: "Is my proposal valid, Can i use my proposal and get the same results than first query?"

No, they are not the same. Let's rewrite your two queries in following way, so we can concentrate on join condition:

SELECT cycle
FROM   product AS pr
INNER JOIN productdetails AS prd ON  pr.cycle = prd.cycle OR prd.cycle LIKE pr.cycle+ '-EXECUTED%'
WHERE prd.label = 'XRT354511' ;

and

SELECT cycle
FROM   product AS pr
INNER JOIN productdetails AS prd ON  pr.cycle = prd.cycle
      OR (pr.cycle = prd.cycle AND pr.cycle LIKE '-EXECUTED%')
WHERE prd.label = 'XRT354511' ;

The first condition is

pr.cycle = prd.cycle OR prd.cycle LIKE pr.cycle+ '-EXECUTED%'

The second (your proposal) is

  pr.cycle = prd.cycle
    OR (pr.cycle = prd.cycle AND pr.cycle LIKE '-EXECUTED%')

This latter is equivalent to 

 pr.cycle = prd.cycle OR pr.cycle LIKE '-EXECUTED%

As you can see easily in this simplified form, in the first you look for (eg. if pr.cycle='100') '100' and '100-EXECUTED%'. In the latter you look for '100' (or pr.cycle LIKE 'EXECUTED%')

If you post sample data, I can try to see results with you and try analyze performances.

Upvotes: 1

MtwStark
MtwStark

Reputation: 4058

the answer to your 1st question is NO, your solution will get diiferent results than original query

to optimize the query you should analyze which indexes are active on your product and productdetails tables..

you can try;

SELECT cycle
FROM product AS pr
INNER JOIN productdetails AS prd ON pr.cycle = prd.cycle 
WHERE prd.label = 'XRT354511' 

UNION ALL

SELECT cycle
FROM product AS pr
INNER JOIN productdetails AS prd 
ON  SUBSTRING(prd.cycle, 1, LEN(pr.cycle)+9) COLLATE LATIN1_GENERAL_BIN = (pr.cycle + '-EXECUTED') COLLATE LATIN1_GENERAL_BIN
WHERE prd.label = 'XRT354511' 
AND pr.cycle <> prd.cycle

This will split it in two queries, the 1st will probably use an index and the 2nd should be slight fast then the like operator

But you also told that the position of "-EXECUTED" should not be important in productdetails.cycle

In this case you need to change the 2nd join condition to

ON  prd.cycle COLLATE LATIN1_GENERAL_BIN LIKE (pr.cycle + '-%-EXECUTED%') COLLATE LATIN1_GENERAL_BIN

please note the "-%" before "-EXECUTED"

also note the last condition of 2nd query, you COULD try to change the UNION ALL with a simple UNION and remove AND pr.cycle <> prd.cycle

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

Could you change OR part with this one?

OR (pr.cycle = LEFT(prd.cycle, LEN(pr.cycle)) AND prd.cycle LIKE '%-EXECUTED%')

Upvotes: 1

Related Questions