August 17, 2004 at 9:04 am
Hi,
I've just inherited this system involving a reasonably large (25Gb) database. I've run some performance analysis tools on it due to complaints of it running slowly.
One query in particular is giving me some headaches as it is coming up as the worst performing query in the system, yet I can't see why. Here's the query:
SELECT MyTable.UID FROM MyTable WHERE MyTable.UID = 1001
Now before anyone says "Why are you doing this?" part of the system retrieves unique ids and has to validate them to make sure they are genuine and have not been tampered with. The UID column is an identity column and has a clustered index created on it.
The only reason I can think is that MyTable is written to and access very frequently, especially when the system is in full flow. The fill factor on the table is currently set to 90%, which I'm a little curious about due to the shear volume of inserts going on.
Is there a better, more efficient way to do this query? Are there any database changes I could possibly make to improve things?
TIA,
Haydn
August 17, 2004 at 9:29 am
I don't see problem with this query since it already has proper index defined in the table. You may play around by changing the clustered index to non-clustered index.
I suspect the problem is due to the blocking since it is accessed very frequently.
August 17, 2004 at 1:09 pm
Look at the execution plan in QA to see what it is doing.
August 18, 2004 at 9:03 am
If you're not worried about honouring any locks then use
SELECT * FROM [TBL] WITH(NOLOCK)
WHERE ID = @ID
August 19, 2004 at 7:33 pm
I don't suppose its as simple as a fragmented index? The previous 2 responses are definite possibilities, though. If execution plan shows it using the wrong index and you can't figure out why, an index hint might fix it; not that I consider index hints to be a good practice.
August 20, 2004 at 1:59 am
I've reindexed the database and used the WITH (NOLOCK) command (forgot about that one). Seems a little better now.
Thanks for the replies.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply