SQL query - matching data between two tables

Question

I have 2 tables, ie;

In TableA there is around 10million rows,
In TableB there is around 500k rows

TableA (10million rows) Url ------------------------------------------- http://www.example.com/data/tuesday-morning http://www.example.com/data/wednesday-evening TableB (500k rows) Keyword Value --------- ---------- Sunday 0 Monday 0 Tuesday 0 Wednesday 0

I want to search all keywords in TableB in TableA, and find the matches, which one matches to update their Value to 1.

I use MERGE, but the problem is it takes at least 10 hours to make that search.

I will make that search everyday, because the KEYWORDs are updating daily in TableB

MERGE INTO TableB As TB USING (Select Url From TableA) As TA ON TA.Url LIKE 'http://www.example.com/data/'+TB.Keyword+'-%' WHEN MATCHED THEN UPDATE SET TB.Value=1;

What will be the best SQL query to make the fastest lookup between these 2 tables?

Many Thanks

Get rid of (Select Url From TableA) that for sure slows down your query, use just TableA — gofr1
– gofr1, Commented Aug 22, 2016 at 12:50
With this row amounts the only way - use full text indexes. That is - take approach proposed below by tinka (stackoverflow.com/a/39080778/2746150), but you have to replace like '%'+t2.keyword+'%' with language constructs specific to full-text indexes which are way faster. — Anton Krouglov
– Anton Krouglov, Commented Aug 22, 2016 at 13:33

wiretext · Accepted Answer · 2016-08-22 13:23:34Z

if i understand your Q might be this solution will help you, you can apply some WHERE clause by ID or something so you can rectify whats going on with your record first apply with few data then you can apply with your all data.

 -- declare table1 declare @table1 table (url varchar(max)) insert into @table1 values ('http://www.example.com/data/tuesday-morning'), ('http://www.example.com/data/tuesday-morning'), ('http://www.example.com/data/noday-morning') -- declare table2 declare @table2 table (keyword varchar(33), val int) insert into @table2 values ('monday',0), ('tuesday',0) -- select select * from @table1 t1 join @table2 t2 on t1.url like '%'+t2.keyword+'%' -- update update @table2 set val =1 from @table1 t1 join @table2 t2 on t1.url like '%'+t2.keyword+'%' -- select again select * from @table1 t1 join @table2 t2 on t1.url like '%'+t2.keyword+'%'

Collectives™ on Stack Overflow

SQL query - matching data between two tables

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related