May 20, 2009 at 11:40 am
Hello Everyone,
We have a small table on which we execute queries:
[font="Courier New"]Column_name Type Length Nullable
------------- --------- ------- ---------
sysaccess smallint 2 no
stat_no int 4 yes
employee_id varchar 8 yes
start_date datetime 8 yes
cumul varchar 10 yes [/font]
In the WHERE CLAUSE of the query we have all the columns used by the clustered index on this table.
Here is a "generic" query:
[font="Courier New"]select cumul from empscumul where stat_no = 11 and employee_id = '8119' and start_date = '20070301'[/font]
The non-unique clustered index is on columns : stat_no, employee_id and start_date.
When we have approx. 4 millions rows in the table : 12 rows per stat_no, employee_id and start_date, the query execute in less than 5 ms.
When we have approx. 16 millions rows in the table : 50 rows per stat_no, employee_id and start_date, the query execute in 20 ms.
What can explain a so huge difference since we are using the clustered index (seek) to find the corresponding rows and than return them: 12 rows in one case and 50 rows in the other case?
Can it be just because the server have more rows to return?
Regards.
Carl
May 20, 2009 at 11:57 am
4 million rows * 4 = 16 million rows
5 ms * 4 = 20 ms
Seems like your query is performing in a fairly linear progression. Granted I've never worked with that many rows, the largest table I've ever had to query was approx 8 mil rows, but I would think that getting your expected results in 20 miliseconds is pretty decent.
-Luke.
May 20, 2009 at 12:32 pm
Thank's Luke,
With an index (in this case a clustered index) I would not expect a linear progression.
Regards.
Carl
May 20, 2009 at 12:39 pm
I'm by far not a tuning expert, and threw in my 2 cents only so that I would get to read some of the discussion and have a vested interest.
Without the execution plan I'm just guessing and for some reason when I read non-unique, I was thinking non-clustered, and was thinking about bookmark looks ups and extra pages being read into memory and such...
-Luke.
May 20, 2009 at 12:46 pm
Hello Luke,
The execution plan is : Clustered Index Seek.
Best regards.
Carl
May 20, 2009 at 1:00 pm
I confirm Luke. A linear behavior seems to be well in my opinion.
Anyway, 20ms seems to be a not a good performance (sure, depending on the hardware). I'm quiet sure this depends on your VARCHAR column within the primary key.
I just tried on our production box at a table with more than 1/2 billion rows and a primary-key over two INT columns; it took 4ms
May 22, 2009 at 4:55 am
could you post the actual execution plan?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 7:00 am
Sure,
here is the execution plan text:
Clustered Index Seek(OBJECT: ([test_carl].[dbo].[empscumul].[idxa]), SEEK: ([test_carl].[dbo].[empscumul].[stat_no]=CONVERT_IMPLICIT(int,[@1],0) AND [test_carl].[dbo].[empscumul].[employee_id]=[@2] AND [test_carl].[dbo].[empscumul].[start_date]=CONVERT_IMPLICIT(datetime,[@3],0)) ORDERED FORWARD)
Best regards.
Carl
May 22, 2009 at 7:36 am
Thanks.
sorry I'm not great with that type of output sorry.
Acount you save the graphical plan and attach it
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 7:51 am
I don't know how to attach a file in a post.
But the execution plan is a simple clustered index seek.
Regards.
Carl
May 22, 2009 at 7:59 am
just seems strange cause for this query
select cumul
from empscumul
where stat_no = 11
and employee_id = '8119'
and start_date = '20070301'
The column cumul is not in your clustered index so I would expect a bookmark look up or something else.
if you right click the graphical plan and save as then zip it up and then when you post
all you have to do is look up the message icons below the post reply, spell check and preview buttons you will see an attachments section
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 8:28 am
Here is a clip of the execution plan.
Best regards.
Carl
May 22, 2009 at 8:50 am
thanks.
Wow that is strange unless I'm missing something (very possible) being late Friday afternoon.
I'm not 100% what other advice to give.
Oh and that is the ACTUAL EXECUTION PLAN right and not the estimated execution plan.
Also normall it's better to attach the actual plan so we can hover and see the predicates and whats actually happen, as with the picture we can only see the operators which is only half the info.
I'm gonna see what I can do with test data
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 8:58 am
Sorry yip friday is getting to me, there are no joins to other tables hence no look-ups
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 9:04 am
Thank's Christopher,
here is the ACTUAL execution plan (.sqlplan zipped).
Best regards.
Carl
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply