LIKE operator with millions of records giving slow performance

Question

LIKE operator with millions of records giving slow performance

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Raj Deep

Please could you provide a sample table with a few rows containing data similar to that found in your exclusion table? It should be in a format like this:

CREATE TABLE #Exclusions (ExclusionID INT, ExcludedURL VARCHAR(200))

INSERT INTO #Exclusions (ExclusionID, ExcludedURL)

SELECT 1, 'C:\Program Files\Microsoft SQL Server' UNION ALL

SELECT 2, 'C:\Program Files\Microsoft SQL Server\100\COM' UNION ALL

SELECT 3, 'C:\Program Files\Microsoft SQL Server\100\KeyFile' UNION ALL

SELECT 4, 'C:\Program Files\Microsoft Office' UNION ALL

SELECT 5, 'C:\Documents and Settings' UNION ALL

SELECT 6, 'C:\Program Files\Microsoft Office' UNION ALL

SELECT 7, 'C:\Program Files\Microsoft Visual Studio'

A few tens of rows should be sufficient.

Many thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Raj Deep Alapati Ten Centuries Points: 1118 More actions · Answer 2

Here is the sample data

%://llnw-1083.listen.com/6%.rad%

%://fe5357.sidestep.com/servlet6/%

%://cdn-62.blinkx.com/stream/%.flv%

%://genio.virgilio.it/scripts/alice/ifr_virg_comu_genio_cachebust.js_728x90_rmt931648.html%

%://download462.mediafire.com%

%://www.asus.com.au/websites/au/xml/localization.xml%

%://msnvideoar.vo.llnwd.net/d1/stupidvideos/dog_taps_paw_in_time.flv%

%://www.princes-trust.org.uk/crossdomain.xml%

%://www.shockwave.com/content/sol_addiction/sis/addict.dat%

%://quality-s.qvc.comquality-s.qvc.com%

I understand the URLName starts with % and indexes wont work when it finds %. thus why i am thinking to use FullText Indexes but not sure which search like 'CONTAINS' OR 'FREETEXT' works in this context.. and on which table and column which i should apply the full text indexes. I want to be sure That % is treated as the way when we generally use LIKE operator..

I just read this article.. any ideas if it can help me

http://www.sqlservercentral.com/Forums/Topic669022-338-1.aspx#bm669714

Thanks

Raj Deep

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

DROP TABLE #Exclusions

CREATE TABLE #Exclusions (ExclusionID INT, ExcludedURL VARCHAR(200))

INSERT INTO #Exclusions (ExclusionID, ExcludedURL)

SELECT 1, '%://llnw-1083.listen.com/6%.rad%' UNION ALL

SELECT 2, '%://fe5357.sidestep.com/servlet6/%' UNION ALL

SELECT 3, '%://cdn-62.blinkx.com/stream/%.flv%' UNION ALL

SELECT 4, '%://genio.virgilio.it/scripts/alice/ifr_virg_comu_genio_cachebust.js_728x90_rmt931648.html%' UNION ALL

SELECT 5, '%://download462.mediafire.com%' UNION ALL

SELECT 6, '%://www.asus.com.au/websites/au/xml/localization.xml%' UNION ALL

SELECT 7, '%://msnvideoar.vo.llnwd.net/d1/stupidvideos/dog_taps_paw_in_time.flv%' UNION ALL

SELECT 8, '%://www.princes-trust.org.uk/crossdomain.xml%' UNION ALL

SELECT 9, '%://www.shockwave.com/content/sol_addiction/sis/addict.dat%' UNION ALL

SELECT 10, '%://quality-s.qvc.comquality-s.qvc.com%'

How much of each URL do you want to match up to the other table? Do you have a set of rules?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Raj Deep Alapati Ten Centuries Points: 1118 More actions · Answer 4

I dont have any rules in between .. It was as similar to URLName like URLExclusionName( because the URLExclusionName already starts with % and ends with %). There was nothing like how much i should match..you can consider the URLExclusionName as search criteria included

Thanks

Raj Deep

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Raj Deep Alapati (7/30/2010)
I just read this article.. any ideas if it can help me
http://www.sqlservercentral.com/Forums/Topic669022-338-1.aspx#bm669714
Thanks
Raj Deep

The article you've linked to points out that a LIKE comparison can be optimised if there's a wildcard at one end only, which would usually be the right hand end of the word. Do you think it would be possible to remove garbage from the left hand side of your URL expression, in both tables?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Raj Deep Alapati Ten Centuries Points: 1118 More actions · Answer 6

Hi Chris,

Right now i want to keep this idea as the last option.currently i am seeing if full text index works for me .. Any suggestion how can i accomplish this

Thanks

Raj Deep