Databases
StackOverflow2013
Postsdbo
POTSQL - query | Step by Step very fast - but as one query very slow
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTSQL - query | Step by Step very fast - but as one query very slow
    primarykey
    data
    text
    <p>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 ,) ........</p> <p>This select on my SQL Server 2008 R2 x64 takes about <strong>1min and 16secons</strong>:</p> <pre><code>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) &gt; 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) &gt; 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) &gt; 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) &gt; 0 </code></pre> <p>this code takes just a <strong>few milliseconds</strong>..</p> <pre><code>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) &gt; 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) &gt; 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) &gt; 0 </code></pre> <p>....yep! You're right I just removed one of my 'OR blocks'... but it doesn't matter which one I remove</p> <p>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)</p> <p>And now is comes...</p> <p>This one is also very fast (<strong>less than one second</strong>) and I have included all 'OR blocks'</p> <pre><code>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) &gt; 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) &gt; 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) &gt; 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) &gt; 0 select * from @guidTable </code></pre> <p>...i just split the selects and write the results query by query into a new declared table...</p> <p>Currently I sovled this tasks by creating a function in sql - but who can tell me what could be the problem?</p> <p>(btw: the DB has currently just about 5000 entries)</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLieven Keersmaekers
    1. USCadburry
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHave you looked at execution plans and IO and TIME statistics=?
      singulars
      1. POTSQL - query | Step by Step very fast - but as one query very slow
      1. USmarc_s
    2. COthank's - as commentet in answer 1 the use of 'exists' dont impove the performance. Btw: there is nothing to join at: join T_RECIPIENT_SMS on T_RECIPIENT_SMS - the table is empty - but if i remvoe it, it's fast.. as fast as i remove or add one with data... i'm gonna take a look at the execution plan...
      singulars
      1. POTSQL - query | Step by Step very fast - but as one query very slow
      1. USCadburry
    3. COthe execution plan told me to add an index.. but nothing changed. ...strange because its fast when i remove the select to an empty table... is there a way to post the execution plan 'sqlplan' file? or do i have post a link?
      singulars
      1. POTSQL - query | Step by Step very fast - but as one query very slow
      1. USCadburry
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload