October 21, 2008 at 4:12 pm
Hi everyone I know this is gonna sound weird but I run a query on a table that has over 300 thousand rows and sql server 2K5 performs poorly. I run this query on sql server 7.0 and 2k5. In sql server 7.0 it takes 8-10 secs and in 2K5 it takes 10-13 secs; my table hasn't gotten any indexes, so it just does a table scan. The execution plan shows that sql server 2K5 is cheaper but I can't understand why it takes longer time. Any ideas are appreciated, thanks.
October 21, 2008 at 4:17 pm
cache? Are you clearing this before testing?
That's not much of a difference though. Could be other factors.
October 21, 2008 at 4:37 pm
Steve Jones - Editor (10/21/2008)
cache? Are you clearing this before testing?That's not much of a difference though. Could be other factors.
I am not clearing the cache before doing this, is there an option where to disable this option? yeah I know it is not much a difference but when that is a difference between just in a query inside a group of queries, it's a big delay there.
Well actually the times in sql 2K5 went from 16 secs to 8 - 13 secs but in the sql 7.0 went from 13 to 7-10. That is after running the query several times (30 times approx). So I could say that if I keep running the query I will get a better performance?
Thanks again.
October 22, 2008 at 6:01 am
It's just a scan against a heap? You won't see any real improvements there until you change the situation, adding a clustered index, or an index, period. 7.0 might just be faster in this situation. One other thing to check though is the physical system. Is the memory the same, more or less, is the cpu the same, more or less?
Also, the "cost" of an execution plan is an estimated cost based on the statistics stored, or generated, with the table or indexes being accessed. If the statistics are off, the cost estimate will be too. Heck, it can just be a bit inaccurate. It's a good starting point for troubleshooting, tuning, etc., but it's not a perfect measure and actual execution times, followed by I/O counts, is the best measure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply