Re: Weird speed problem

On Oct 26, 7:39 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
DBMonitor (spamawa...@xxxxxxxxxxxx) writes:
Both query plans use indexes on for both the tables though. The plans
are almost identical.

"Almost". Apparently, there is a subtle, but important difference.

Would it be posible for you to post the query plans? If you are on SQL 2005
you can save the graphical execution plan in a file and post that in an
attachment. (Or put it on a web site with a link to it.)


The query plans are as follows:

-----------------
Subquery Query (Total cost 41.52%)
SELECT (0%)<-CS (0%)<-HM IJ (68%)<-IS Col1 (11%)
..................................<-IS Col2 (20%)

Standard Query (Total Cost 58.48%)
SELECT (0%)<-CS (0%)<-HM IJ (78%)<-IS Col1 (8%)
..................................<-IS Col2 (14%)

KEY:
CS - Comput Scalar
HM IJ - Hash Match/Inner Join
IS - Index Seek

-------------------------

The main difference is the hash match process. For the sub querys, the
row size is 15 for the query with the sub queries and 21 for the
standard query however the sub query query has more rows on it.

The only thing I can think of which is happening is some sort of page
io sharing problem. The database server is running on a virtual
machine and the last wait type for the queries are always
'PAGEIOLATCH_SH' and the process runs on one thread.

When I run it on a dedicated server, the times to run the queries are
almost identical and the lastwaittypes change to CXPACKET and uses
multiple threads.

.

Relevant Pages

  • Re: union all
    ... I can give you table structure and query plans, ... management for posting data. ... Give the estimated execution plan for the two queries by running the ...
    (microsoft.public.sqlserver.programming)
  • 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: IS NULL not working in WHERE clause.
    ... The good query on the big table. ... You can also try adding an index hint to see if that helps. ... the query plans are different, add a hint to mention the name ... after the order by clause. ...
    (microsoft.public.sqlserver.programming)
  • Re: LIKE vs Equals
    ... > The query plans are different. ... > operates on string data, ... > to string types, and this could result in differences from the = ...
    (microsoft.public.sqlserver.programming)
  • Re: Weird speed problem
    ... query first and the first query last, or if you run both queries ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ... Both query plans use indexes on for both the tables though. ...
    (comp.databases.ms-sqlserver)