November 26, 2009 at 7:09 pm
I was trying to improve performance of my little project and found a lot of instances where the optimizer was doing a partial index seek and then a key lookup/index scan. Aha! I thought and went about tweaking my indexes.
I now have the situation where everything is accomplished with index seeks, great yes? NO. Without getting into a debate about how inefficient my coding is ( I am a newbie ) prior to the tweaks the system was churning through 10 whole rows of data a second but now it has dropped to about 5.
I have not changed a single line of code so I am really confused as how more efficient indexes have slowed it down. These are not even new extra indexes just originals I had already created but extended and they are obviously correct due to only index seeks being done.
Any king gurus got any pointers it would be greatly appreciated. Non-gurus also welcome.
November 27, 2009 at 10:26 am
Some further info regarding my problem.
I broke down my code into individual chunks and ran them seperately against a 1000 records. Most of the chunks completed in 10 to 15 seconds and then were a couple that took about 45 seconds. I went back to run them a second time just to make sure of the timings and then those taking 10/15 now completed in less than a second and the others in around 5 seconds.
I started from scratch and rebuilt evrything and had exactly the same results.First time any query was run took about 20 times longer than subsequent queries, I could even hear my system crank up to full tilt on the first round and then sit like a mouse for round two.
For one final check I then ran the same queries on the same table but using different data rows and wham bam! back to snail pace.
The same indexes are being shown as used in the actual execution plan but it seems to me that although the index 'exists' it doesn't have any data in it, as though when I initially create it the structure is there but it's not populated with anything. So I run the query first time and the index gets updated, but only for that data.
What am I missing? I build the table, bulk import my data and then create the indexes. Do I have to create before importing the data?
November 27, 2009 at 1:08 pm
Please post query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ It's really hard to talk about performance in abstract.
Are the indexes fragmented?
First execution of a query will be slower than subsequent executions. Data has to be fetched off disk into the data cache, execution plan has to be generated and cached.
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
November 30, 2009 at 7:54 am
Sounds like you're running a cursor. But I'm honestly just guessing without code.
"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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply