December 11, 2009 at 9:30 am
Query A
UnCached does 2+ Million logical Reads with lots of scans and takes over 2 minutes to execute
Cached reads etc are same, but execution time is 5 seconds
Query B
UnCached does 25,000 logical reads with very few scans and executes in 30 seconds
Cached is taking 10 seconds
Query B is an altered version of query A in the sense that I removed some unused variables from the where clause. My brain is telling me that if it does 10 Times fewer reads that Query A, Query B should run much faster in Cache than Query A. But the results indicate that a poorer performing query actually runs faster in cache.
Any thoughts on why my better written query is slower in cache?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
December 11, 2009 at 9:46 am
Please post queries, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Logical reads aren't the only measure of query performance. They are usually a reasonably good one, but there are other considerations.
I would guess the first is so slow on the first run because of all the physical reads that it's performing. Disk = slow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2009 at 2:20 pm
I agree with Gail. There is no way to troubleshoot this without sufficient information on your environment and the queries you are troubleshooting. Please post eveything you can in order to get some usable feedback.
Joie Andrew
"Since 1982"
December 14, 2009 at 2:02 pm
Looked at the execution plan further. The "bad" query that was doing more logical reads...but it was doing index seeks and loop joins. The "good" query that was doing far fewer logical reads is doing index scans and hash joins. I think that is why the bad was able to cache more easily.
Now getting to the hash join in this query...is it avoidable?
SELECT B.col5, B.col6, A.col3
FROM dbo.A
inner JOIN dbo.B ON A.col0 = B.col0
WHERE A.col4 = 5
AND A.col5 = 'Y'
AND B.col1 = 911
AND B.col2 = 'N'
AND B.col3 = 18
execution plan:
|--Hash Match(Inner Join, HASH: ([A].[col0])=(.[col0]), RESIDUAL: ([A].[col0]=.[col0]))
|--Index Seek(OBJECT: (indexA AS [A]), SEEK: ([A].[col4]=5), WHERE: ([A].[col5]='Y') ORDERED FORWARD)
|--Index Seek(OBJECT: (indexB AS ), SEEK: (.[col1]=911), WHERE: (.[Col3]=18 AND .[Col2]='N') ORDERED FORWARD)
Rows in A based on criteria: 1,435,974
IndexA = a.col4, a.col0, a.col3, a.col5
Rows in B based on criteria: 1,841,709
IndexB = B.col1, B.col5, B.col6, B.col2, B.col3, B.col0
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
December 14, 2009 at 2:51 pm
myplace2sell (12/14/2009)
Now getting to the hash join in this query...is it avoidable?
Should you be trying to avoid it with the row counts that you mentioned?
http://sqlinthewild.co.za/index.php/2009/11/24/the-most-optimal-join-type/
If the row counts were the same all the time, the one with the loop join probably has a cardinality mis-estimation. Loop joins should not appear for that number of rows, they're just not optimal at that point.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply