SQL Performance Issue
-
Hello,
Let me explain the database table model I'm working with so you can understand my question. I have a table with 3 million records and one Primary Key that gathers 3 columns. These columns, in order, produce the following result rows: 1IDX_Column - 100 rows; 2IDX_Column - 10000 rows; 3IDX_Column - 500000 rows.
Prior to this post I have allready modified the Primary Key definitions to the one above and it began returning the single record at 1/3 of the time it spent before (before that I had the inverse column order defined as Primary Key). Logic - Smaller to larger group search!!
Now my question is : Does anyone know why SQL takes more time to process a simple «SELECT» call when the 3IDX_Column group produces more rows ? This is, if the 3IDX_Column produces 500000 rows it takes 0.2seconds to fetch the row! If the 3IDX_Column produces only 20000 rows it takes about 0.01seconds to fetch the same row. Keep in mind that the 1IDX_Column and the 2IDX_Column always produce the same amount of rows.
Is there any gadget to prevent this performance hole ?
My best regards to you all
-
Is the primary key on the 3 columns clustered or non-clustered ?
Any other indexes on the table ? (Did you display the execution plan each time to verify what method is being used ? )
-
When you say the index produces rows, are you talking distinct values?
If so, then the index that produces less rows will take longer to find a distinct row, because it has to do a sequential scan through all rows which contain a particular value. If there are less distinct values, then there are more rows to scan for a particular value, and so it takes longer.
-
No. Just the Primary Key, being the aggregation of the 3 indexes, produce a distinct value. The individual columns produce rows in the amount specified in the first post.
From the experiences I have run 'till now I've noticed that the smaller the size of the first distinct grouping of values that appear in the PK, like in the first post, the faster it gets the row I'm searching for.
Thanks for any help you can give me
-
My PK is clustered and it is the only index in the table. The command I have been using is the most simple command. Notice that I'm running these commands from a SQLClient provider in .NET - either using a DataAdapter or a DataReader or just using the ExecuteScalar to return a single object has always the same relation in time permormance according to the first post.
The command : «SELECT db.(Desired Values)... FROM db WHERE db.1IDX_Column=X AND db.2IDX_Column=Y AND db.3IDX_Column=Z»
I have placed a counter/logger for each execution plan but the time performance results only differ when the amount of rows produced by the 3IDX_Column also differ.
Thanks in advance for any help on this
-
Check dbcc spaceused, to find out the index-statistics for your table/index.
dbcc dbreindex yourtable will optimize spaceusage of your table when you'r using a clustered index.
sp_updatestats, dbcc updateusage may help to optimize sqlserver's access.
set stats io on may also help you to figure out what it's doing.
Are there any other users using the server at that time (locking, io-wait, processor-capacity,...)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply