Using REGEXP in a TABLE using another TABLE as pattern (too slow)

Question

First thanks for your help.

I just started working with tsql and I got a problem with performance.

I have two tables table1 and table2, table2 give me a lot f patterns that I got check if I find in each row of table1, if it happen I save to another column in table1.

So I get this string transform into a regexp and use "Like" expression like this

UPDATE table1 AS tb1
SET CASE 
    WHEN tb1.ft IS NOT NULL tb1.ft = tb1.ft + ',' +tb2.[as]
    ELSE tb1.ft = tb2.[as]    
FROM table1,
INNER JOIN (
    SELECT DISTINCT [as]
    FROM table2
           ) AS tb2
ON fn like '%' +tb2.[as]+'%' OR fp like '%' +tb2.[as]+'%'

Do you guys think there is a way to improve performance? I Kind of got stuck in this solution

p.s: I didn't tested the update, only a select.

score 3 · Answer 1 · answered Mar 01 '18 at 00:34

First, proper syntax would look more like this:

UPDATE t1
    SET ft = COALESCE(tb1.ft + ',' +tb2.x, tb2.x)
    ELSE tb1.ft = tb2.x  
FROM table1 t1 INNER JOIN
     (SELECT DISTINCT [as] as x
      FROM table2
     ) t2
     ON t1.fn like '%' + tb2.x + '%' OR t1.fp like '%' + t2.x + '%';

Performance will still be bad, but this might, at least, do what you want.

As for further performance improvement, I suspect you will need to give up on that. This is very difficult to improve in SQL Server. I note that you appear to be constructing a comma-separated list. This indicates a very poor data model. So, there might be ways to solve your problem with a different data model. If you are interested, ask another question with more details about what you are attempting to accomplish -- and provide sample data and desired results.

score 0 · Answer 2 · answered Mar 01 '18 at 17:49

Without knowing the volume/size of the tables, it is hard to evaluate query performance. I usually separate query to temp table steps. if the data set is small for each temp tables.

-- put to temp table t2
SELECT DISTINCT [as] as x 
into #t2
FROM table2;

-- put to temp table t3
SELECT tb1.id, tb1.ft, #t2.x into #t3
FROM table1 t1 INNER JOIN #t2 on t1.fn like '%' + #t2.x + '%' OR t1.fp like '%' + #t2.x + '%';

--collapse it as Gordon Linoff or try the following methods
--refer to https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv
UPDATE t1
SET ft = COALESCE(tb1.ft + ',' +tb2.x, tb2.x)
        ELSE tb1.ft = tb2.x 
FROM table1 t1 INNER JOIN #t3 on t1.id = #t3.id

refer to How to concatenate text from multiple rows into a single text string in SQL server? for collapsing.

Please find the clear version in my SQL Editor https://sqleditor.net/q/HJIWTnSuM

Using REGEXP in a TABLE using another TABLE as pattern (too slow)

2 Answers2