QOD 21 Nov

Question

QOD 21 Nov

happycat59

One Orange Chip

Points: 29389
More actions
November 20, 2003 at 11:32 pm

#107104

"Drop the primary key clustered index and create a new one on LAST_ORDER_DT" implies to me that you are removing a constraint (PK constraint on something that really ought to have a PK constraint on it).
Perhaps this answer should have been worded a LOT better so that it
e.g. Make the primary key non-clustered and create a cluster index on LAST_ORDER_DT.
As it stands I firmly believe that the supposedly correct answer is very wrong.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

I entirely agree. There is no way I'd create a clustered index on a volatile key such as LAST_ORDER_DT. This is something you just don't do with SQL 7.0/2000, as any change to LAST_ORDER_DT will cause leaf level rows to be moved around (including possible page splits) and all non-clustered index entries will be affected too.

IMHO a non-clustered index is the best option.

Cheers,

- Mark

Cheers,
- Mark

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

I entirely agree happycat59's comments. Plus the fact each clustering index should be picked, based on application-needs. You don't change clustered ix just because a query runs slow. First stage would be to support it with a nonclustering index and then evaluate clustered index changes.

Then again because it concers your main query, executed every 1 second, there is more to do then just altering the clustering algorithm. You also should implement frequent index-rebuilds and of course keep the PK-constraint.

Edited by - alzdba on 11/21/2003 12:29:23 AM

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

StudyingSQL Grasshopper Points: 16 More actions · Answer 3

Who comes up witht his questions. You guys on the forum are absolutely right.

This magazine should come to the forum and post an appology

...and gives us our two points darn it.

Rob_T SSC Enthusiast Points: 176 More actions · Answer 4

Also, you dont just drop a primary key without investigation. After all that key might be driving the most used query on the system.

mia Right there with Babe Points: 749 More actions · Answer 5

Well, I answered the question correctly but reading these posts am kind of embarrassed not to have thought more about it and got it wrong instead V interesting discussion!

Cheers,
mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

Keiths123 SSCommitted Points: 1789 More actions · Answer 6

I'm with all of these guys. You can't create a primary key clustered index on the LAST_ORDER_DT as it won't be unique.

Unless you have had one order a day for the last million days and it is now the year 4751, then I would agree with you.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 7

quote:
Which of the following could be done to improve performance?

happycat59: It does not speak at all about the needs of other constraints, it's just a question about what might speed up the poor query...

alzdba:

quote:
Plus the fact each clustering index should be picked, based on application-needs.(...)You don't change clustered ix just because a query runs slow

Oki, in your opinion, what is application needs? Are they never relying on queries?

StudyingSQL: Apology? If it would be deemed incorrect, maybe they could come with a correction. How much do we actually PAY for the QoD? Do you only answer the QoD's for getting your points? What about doing them for intriguing your brain to see which areas of SQL you know much or little about?

Keiths123: Probably LAST_ORDER_DT is a DATETIME field. Since the precision of a DATETIME field is one three-hundredth of a second you can have upto 300 UNIQUE records per second. 300 * 3600 * 24 gives you around 26 Million unique records per day and in theory all your 1.000.000 could be during the same day. Ok, I agree that you shouldn't rely on a DATETIME field to be unique but you could def. have more then one order per day.

Regards, Hans 😉

Edited by - hanslindgren on 11/21/2003 03:36:41 AM

bustell Ten Centuries Points: 1209 More actions · Answer 8

I agree with everyone on this post. I would so far as saying that answer is incorrect.

The question implied that you were dropping the primary clusted index on the company Id. That is clearly a bad thing to do. You do not want to drop the primary key, just possibly the clusted option of the key.

Only after carful investation of whether the company Id or the LAST_ORDER_DT would benefit more from a clusted index, should you remove the clusted index (leaving the primary key) from the company Id and creating a clusted index on LAST_ORDER_DT.

I want my points back!!

Pat Buskey

macsgs SSC Enthusiast Points: 131 More actions · Answer 9

A question: Would it really be too much trouble to edit properly the questions before sending them out in the Daily Update emails? Having to check the wording of answer options provided in the Update against the wording on the QOD web page is irritating, and indicates editorial sloppiness.

Supplementary question...

Is "anal retentive" properly spelled with a hyphen?

bdohmen SSCrazy Points: 2996 More actions · Answer 10

I too answered incorrectly. Looking at the wording of the question("which of the following could be done") means both answers can be correct.

The fact the query runs every second implies the data changes very rapidly. In other words, I would expect several update statements to run against this field every second. I would also expect modifying the clustered index would move the bottleneck to these update statements. So while both statements can be correct, I still believe you are better off creating a non-clustered index on the LAST_ORDER_DT field.

Brian

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

hanslindgren:

quote:

--------------------------------------------------------------------------------