Posted by DBMonitor on 10/25/07 20:41

On Oct 26, 5:48 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 24 Oct 2007 22:13:11 -0700, DBMonitor wrote:
> >Does anyone know of a reason why there would be such a difference in
> >query speed?
>
> Hi DBMonitor,
>
> In addition to the suggestion posted by Roy, have you considered that
> this may be caused by cachhing? In other words, if you run the second
> query first and the first query last, or if you run both queries
> repeatedly, does that change anything to the execution time?
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Thanks for your replys.

Yes, there is an index on column 1 and one on colunm 2 but none for
both. However the user could search on one of about 30 columns on the
table therefore the search is dynamic.

Both query plans use indexes on for both the tables though. The plans
are almost identical.

In order to ensure the cache was not the problem I did a DBCC
DROPCLEANBUFFERS before executing each query. It does not matter what
order they are run also. The query with the subqueries always runs
faster then the standard query.

[Back to original message]