Re: Weird speed problem

On Oct 26, 5:48 am, Hugo Kornelis
<h...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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.

.

Relevant Pages

  • Re: Slow query
    ... Here I can only guess that more columns help to make the query more ... Have you looked at the query plans? ... Which version of SQL Server you are using. ... SQL 2005 or later) as .sqlplan files, and attach them. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL query question #2
    ... and having test data makes it easier to test. ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ... E.g. if I query the above data with required values of 'b' and 'd'. ...
    (comp.databases.ms-sqlserver)
  • Re: Help for a simple (?) query - part 2
    ... "I'm stuck with a simple query. ... ORDER BY LastUsedDate DESC) ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.mseq)
  • Re: Fetching Data from 2 tables and send in text File using query
    ... File so what can be the query to do so. ... How to embed this in DTS is beyond me, ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: How To Return A "Range Of Rows"??
    ... Shouldn't I be seeing results with the column headers? ... this just to show that the query works in debug mode? ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)