CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Cadburry
Cadburry

Reputation: 1864

TSQL - query | Step by Step very fast - but as one query very slow

Can someone please tell me the difference... I don't know what's the problem and don't know how to describe it in a few words to search on stackoverflow on it ,) ........

This select on my SQL Server 2008 R2 x64 takes about 1min and 16secons:

select T_MESSAGE.MSG_GUID from T_MESSAGE
where 
T_MESSAGE.MSG_BODY like @searchpattern
or T_MESSAGE.MSG_COMMENT like @searchpattern
or T_MESSAGE.MSG_SEDERDISPLAYINFORMATION like @searchpattern 
or (select COUNT(*) from T_RECIPIENT 
          join T_RECIPIENT_ANDROID on T_RECIPIENT_ANDROID.RAND_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_ANDROID.RAND_DEVICETOKEN like @searchpattern) > 0   
or (select COUNT(*) from T_RECIPIENT 
    join T_RECIPIENT_IOS on T_RECIPIENT_IOS.RIOS_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_IOS.RIOS_DEVICETOKEN like @searchpattern) > 0
or (select COUNT(*) from T_RECIPIENT 
    join T_RECIPIENT_SMS on T_RECIPIENT_SMS.RSMS_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_SMS.RSMS_PHONENUMBER like @searchpattern) > 0
or (select COUNT(*) from T_RECIPIENT 
    join T_RECIPIENT_SMTP on T_RECIPIENT_SMTP.RSMTP_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_SMTP.RSMTP_ADDRESS like @searchpattern) > 0

this code takes just a few milliseconds..

select T_MESSAGE.MSG_GUID from T_MESSAGE
where 
T_MESSAGE.MSG_BODY like @searchpattern
or T_MESSAGE.MSG_COMMENT like @searchpattern
or T_MESSAGE.MSG_SEDERDISPLAYINFORMATION like @searchpattern 
or (select COUNT(*) from T_RECIPIENT 
          join T_RECIPIENT_ANDROID on T_RECIPIENT_ANDROID.RAND_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_ANDROID.RAND_DEVICETOKEN like @searchpattern) > 0   
or (select COUNT(*) from T_RECIPIENT 
    join T_RECIPIENT_IOS on T_RECIPIENT_IOS.RIOS_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_IOS.RIOS_DEVICETOKEN like @searchpattern) > 0
or (select COUNT(*) from T_RECIPIENT 
    join T_RECIPIENT_SMS on T_RECIPIENT_SMS.RSMS_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_SMS.RSMS_PHONENUMBER like @searchpattern) > 0

....yep! You're right I just removed one of my 'OR blocks'... but it doesn't matter which one I remove

If I have more than 3 (!) the query takes a very long time (btw: also if the result of a block is 0 and not only the result also the table is empty)

And now is comes...

This one is also very fast (less than one second) and I have included all 'OR blocks'

declare @searchpattern as varchar(MAX)
set @searchpattern = 'mysearchstring'

declare @guidTable table
( 
    MSG_GUID UniqueIdentifier
)

insert into @guidTable 
select T_MESSAGE.MSG_GUID from T_MESSAGE
where 
T_MESSAGE.MSG_BODY like @searchpattern
or T_MESSAGE.MSG_COMMENT like @searchpattern
or T_MESSAGE.MSG_SENDERDISPLAYINFORMATION like @searchpattern 
or (select COUNT(*) from T_RECIPIENT 
    join T_RECIPIENT_ANDROID on T_RECIPIENT_ANDROID.RAND_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_ANDROID.RAND_DEVICETOKEN like @searchpattern) > 0   

insert into @guidTable 
select T_MESSAGE.MSG_GUID from T_MESSAGE 
where
(select COUNT(*) from T_RECIPIENT 
    join T_RECIPIENT_IOS on T_RECIPIENT_IOS.RIOS_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_IOS.RIOS_DEVICETOKEN like @searchpattern) > 0 

insert into @guidTable 
select T_MESSAGE.MSG_GUID from T_MESSAGE 
where
(select COUNT(*) from T_RECIPIENT 
    join T_RECIPIENT_SMS on T_RECIPIENT_SMS.RSMS_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_SMS.RSMS_PHONENUMBER like @searchpattern) > 0

insert into @guidTable 
select T_MESSAGE.MSG_GUID from T_MESSAGE 
where 
(select COUNT(*) from T_RECIPIENT 
    join T_RECIPIENT_SMTP on T_RECIPIENT_SMTP.RSMTP_REC_GUID = T_RECIPIENT.REC_GUID 
    where T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID 
    and T_RECIPIENT_SMTP.RSMTP_ADDRESS like @searchpattern) > 0


select * from @guidTable

...i just split the selects and write the results query by query into a new declared table...

Currently I sovled this tasks by creating a function in sql - but who can tell me what could be the problem?

(btw: the DB has currently just about 5000 entries)

Upvotes: 0

Views: 240

Answers (1)

HABO
HABO

Reputation: 15852

This should be faster using exists rather than getting counts that you don't need:

select T_MESSAGE.MSG_GUID from T_MESSAGE 
where  
T_MESSAGE.MSG_BODY like @searchpattern 
or T_MESSAGE.MSG_COMMENT like @searchpattern 
or T_MESSAGE.MSG_SEDERDISPLAYINFORMATION like @searchpattern  
or exists (select 42 from T_RECIPIENT  
          join T_RECIPIENT_ANDROID on T_RECIPIENT_ANDROID.RAND_REC_GUID = T_RECIPIENT.REC_GUID  
    and T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID  
    and T_RECIPIENT_ANDROID.RAND_DEVICETOKEN like @searchpattern)
or exists (select 42 from T_RECIPIENT  
    join T_RECIPIENT_IOS on T_RECIPIENT_IOS.RIOS_REC_GUID = T_RECIPIENT.REC_GUID  
    and T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID  
    and T_RECIPIENT_IOS.RIOS_DEVICETOKEN like @searchpattern)
or exists (select 42 from T_RECIPIENT  
    join T_RECIPIENT_SMS on T_RECIPIENT_SMS.RSMS_REC_GUID = T_RECIPIENT.REC_GUID  
    and T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID  
    and T_RECIPIENT_SMS.RSMS_PHONENUMBER like @searchpattern)
or exists (select 42 from T_RECIPIENT  
    join T_RECIPIENT_SMTP on T_RECIPIENT_SMTP.RSMTP_REC_GUID = T_RECIPIENT.REC_GUID  
    and T_RECIPIENT.REC_MSG_GUID = T_MESSAGE.MSG_GUID  
    and T_RECIPIENT_SMTP.RSMTP_ADDRESS like @searchpattern)

You should also confirm that the joins are supported by appropriate indexes.

Upvotes: 1

Related Questions