Speeding up a select count(*)
-
That's what I thaught... changing the clustered index and or PK on a live system is a hell of a job
. -
Just guess why you were getting slower results I would bet that in the composite PK 'sdate' was not the First column. ORDER matters
The why an extra index helped is because it will only be made up of that single column (narrower than 4)
Cheers,
* Noel -
It depends on the numbers for each column of your index, the way they are spread over your total of rows.
Most of the time, PK-columns are just pointed for their unique combination and potential to be used as an FK.
Because for the unique combination, the order makes no sence, examine the cardinality so you can determin a filterfactor. Place the one that filters most at the first place of your index, ...
Offcourse, there can be usage factors, that may have you change that column-order, but that should be examined too.
If your primary key index is also your clustering index, keep in mind, it will affect all other indexes when you modify it (drop/create) .
If you have - like you sead - many rows, plan your action for downtimes, easy periodes, ..
If you also want to have dat date being usefull, consider changing the columns datatype to (small)datetime ! It will speedup the index, enhance your datacorrectness, ease querying,..
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
-
Also that old saying : "If it ain't broken, don't fix it"
. -
so why have a fire insurance ? It's not burning right now
One part of a dba's job is to prevent (proactive) troubles.
That's one of the reasons why you always have to followup (and wizely keep up with ) servicepacks and fixes.
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
-
You're right, but if I had a slow running query and that potential problem, the slow running query would be handled first... and then maybe that one (unless this is a shipped program).
-
Correct.
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 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply