optimizing query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

optimizing query

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2014-05-30 : 08:31:52

I have table E_M (having index in individualid) with 41 million records init, validating the email by comparing the suspect names from the tables ESW,ESP, I have pasted the query below, which takes 5 minites for processing 1 lakh record. please help me to optimise it.

declare @CNT int,@Start int=0,@End int

select @CNT=Max(individualid) from E_M (nolock)

WHILE(@Start<=@CNT)
BEGIN

SELECT @End=@Start+1000

INSERT into e_m_1(individualid, validationcode)
SELECT
individualID
,15
FROM E_M a (NOLOCK)
INNER JOIN [ESW] b (NOLOCK)
ON(a.email =b.EmailSuspectword )
WHERE individualID >@start AND IndividualID<=@End AND email IS NOT NULL
--Whole part check

INSERT into e_m_1(individualid, validationcode)
SELECT
individualID
,14
FROM E_M a (NOLOCK)
INNER JOIN [ESP] b (NOLOCK)
ON(a.email LIKE '%'+b.EmailSuspectword +'%')
WHERE individualID >@start AND IndividualID<=@End AND email IS NOT NULL

SELECT @Start=@End

END

thanks
Subha

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-05-30 : 11:09:10

A LIKE expression with a leading wildcard (% or _ ) cannot use an index on the column, therefore there's very little you can do to optimize the query you have.

I'd suggest looking at full-text indexing, it is designed specifically for indexing words. If you can be sure that an email address appears as a word, then full-text indexing and the CONTAINS operator should dramatically improve performance. See these for more details:

MSDN full text: http://msdn.microsoft.com/en-us/library/ms142571.aspx
SimpleTalk example: https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
CONTAINS: http://msdn.microsoft.com/en-us/library/ms187787.aspx

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-30 : 11:12:13

In your first inner join you have

ON(a.email =b.EmailSuspectword )

Are the join columns indexed?

Also, in your second inner join you have

ON(a.email LIKE '%'+b.EmailSuspectword +'%')

which even if a.email is indexed will still cause a scan, since the LIKE clause is not a SARG.

subhaoviya
Posting Yak Master

135 Posts

Posted - 2014-05-30 : 11:28:21

Hi both,
thanks a lot for response
No i not have index on a.email, i will create index on email,individualid now.

thanks
friends

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-30 : 11:52:16

Do you know what percentage of emails are NULL? If it is small then it may not matter. But, I was thinking that you could put a filtered index on Email where it is not null. *shrug*

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources