Databases
StackOverflow2013
Postsdbo
POWhy do multiple WHERE conditions slow query rather than speed it up?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy do multiple WHERE conditions slow query rather than speed it up?
    primarykey
    data
    text
    <p>The problem is that the query in question runs very slow when compared to the query run with one or two, rather than all three of its conditions.</p> <p>Now the query.</p> <pre><code>Select Count(*) From SearchTable Where [Date] &gt;= '8/1/2009' AND [Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150)) AND FreeText([Description], 'keyword list here') </code></pre> <p>The first condition is self explanatory. The second uses a UDF to get a list of Zip Codes within 150 miles of 30348. The third uses a full text index to search for the provided words.</p> <p>With only this condition</p> <pre><code>[Date] &gt;= '8/1/2009' </code></pre> <p>The query returns 43884 (table size is just under 500k rows) in <strong>3 seconds</strong>.</p> <p>Using only this condition </p> <pre><code>[Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150)) </code></pre> <p>I get 27920, also returned in <strong>3 seconds</strong>.</p> <p>And with only the full text portion</p> <pre><code>FreeText([Description], 'keyword list here') </code></pre> <p>68404 is returned in <strong>8 seconds</strong>.</p> <p>When I use just the zip code and full text conditions I get 4919 in <strong>4 seconds</strong>.<br> Just the date and full text conditions gets me 9481 in just shy of <strong>14 seconds</strong>.<br> Using the date and Zip Code conditions only gives me 3238 in <strong>14 seconds</strong>.<br> With all three conditions the query returns 723 in <strong>2 minutes, 53 seconds</strong>. <em>(wtfbbq)</em></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USClark
    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
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy do multiple WHERE conditions slow query rather than speed it up?
      1. USKleinux
      1. VTFavorite
    2. VO
      singulars
      1. POWhy do multiple WHERE conditions slow query rather than speed it up?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhy do multiple WHERE conditions slow query rather than speed it up?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSince it takes a full 3 seconds to count the Date field I am wondering if there are any indexes on these columns?
      singulars
      1. POWhy do multiple WHERE conditions slow query rather than speed it up?
      1. USKleinux
    2. CO3 seconds is a LONG time for only 500k rows. Indexes are definitely missing
      singulars
      1. POWhy do multiple WHERE conditions slow query rather than speed it up?
      1. USNotMe
    3. COWish I could have awarded the answer to more than one person, because many were helpful. Showplan revealed that the UDF was definitely the issue. when I dump its results to a temp table and join on that, I get my result in 0 seconds... VICTORY!!... Thanks Stack Overflow homies..
      singulars
      1. POWhy do multiple WHERE conditions slow query rather than speed it up?
      1. USClark
 

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