The Weird and The Wonderful

1-Mar-11 5:05

So, I recently changed jobs (4 months ago) and have found some humorous legacy code in the past. But nothing, nothing comes close to the database horror I found. For several days, the senior architect and my manager have been trying to figure out why our nightly and weekly stored procedures were taking so long as to time out. As it turns out, neither have and dba experience.

Well today the discussion turned to hiring an outsider to audit the tables, index, stored procedures, etc. I kindly volunteered to have a look myself, kindly pointing out my database experience. After 15 minutes I found the problem.

There is a table called trades, it has ~23,000,000 rows, and is almost always queried by tradeid and date columns. Not surprisingly the primary key consists of tradeid and date. I looked at the indexes and was shocked to see that there was a clustered index made up of open_quantity and order_type; values of which are almost always 0 and 0 or 1 respectively and almost never search criteria. I looked back at the primary key, and it was set to non-clustered.

This table has existed as such for years- recording and reporting positions and trends on a public facing website the entire time.

I was told I earned my pay for the week, but I'm not allowed to just go home Confused | :confused:

Update:
We created a clustered index on the id and time column and kept the original clustered index as a non-clustered index. It did solve the problems we were having, primarily when inserts were extremely fast, select queries would become impossibly slow.

"Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
— Hunter S. Thompson

modified on Tuesday, March 22, 2011 10:30 AM

musefan1-Mar-11 6:22

musefan

1-Mar-11 6:22

You should have mentioned how much you have saved them getting in an external auditor to do the job and claim that as a pay bonus!

Don't vote my posts down just because you don't understand them - if you lack the superior intelligence that I possess then simply walk away

wizardzz1-Mar-11 6:25

1-Mar-11 6:25

We get an evenly distributed monthly bonus based on the profitability of our department. Technically, since I saved that cost, the extra profit should be reflected in the monthly bonus pool for everyone. But yeah, I feel entitled to a little more of it this time around. I will probably poke around and bring it up once word gets out the major problem was fixed.

musefan1-Mar-11 6:36

musefan

1-Mar-11 6:36

sounds like a good scheme in general

Don't vote my posts down just because you don't understand them - if you lack the superior intelligence that I possess then simply walk away

AspDotNetDev1-Mar-11 7:26

1-Mar-11 7:26

Think of it this way... you get the respect of your peers. Smile | :)

AspDotNetDev1-Mar-11 7:38

1-Mar-11 7:38

I wouldn't think the clustering vs non-clustering would not be a very big problem. The open_quantity and order_type index could be useful if the goal is, for example, to process all orders which have not yet been processed. Rather than scan 23,000,000 rows, you'd only have to process the 10 unprocessed orders. You sure you found the problem?

wizardzz1-Mar-11 7:59

1-Mar-11 7:59

The open_quantity is never a search criteria. Its value is 0 99.99% of the time, and we never search by open_quantity or order_type, if we did, the order_id and date would be included as well. Have you ever inserted large sets of data into a table that had a clustered index, while the inserted data would have to be physically stored into the middle of the table because of the index? It's quite ugly.

I'm not suggesting eliminating the clustered index, but there's no reason the data need be physically stored in order according to open quantity and order type.

AspDotNetDev1-Mar-11 8:35

1-Mar-11 8:35

You are right, there's not really a good reason to store that type of data using a clustered index. Still, I don't see anything that you've come across which would fix the problem you described. Would inserting a value with a bad clustered index cause all the rows in the table to be moved so that the physical location on the hard drive is stricly enforced (i.e., so rows will be ordered on the HD by the clustered index)? Or is SQL smart enough to insert the data without rearranging it (i.e., by using a tree-like structure)?

wizardzz1-Mar-11 8:37

1-Mar-11 8:37

Clustered indexes physically store the rows in the order provided by the index, as far as I know, it can be quite taxing on resources so I'm not sure what logic they use for it.

AspDotNetDev3-Mar-11 11:23

3-Mar-11 11:23

See here. Basically, the row data is stored with the clustered index, which is stored as a B-tree, so insertion is not an expensive operation (i.e., this should not be the cause of your problem).

Andy Brummer3-Mar-11 10:37

Andy Brummer

3-Mar-11 10:37

A clustered index is by definition used to physically order the table data in the file. Non-clustered indexes are only ordered by the data in the index.

http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/[^]

Curvature of the Mind now with 3D

AspDotNetDev3-Mar-11 11:22

3-Mar-11 11:22

Thanks, that is what I suspected. Looks like the row data is stored with the custered index. Since the indexes, including the clustered index, are stored as B-trees, there should indeed be no huge performance hit (i.e., all the data isn't going to have to be reorganized on disk if a row is inserted into the middle of the clustered index). The only time a performance hit would occur would be during a reindexing of the table (even then, the hit would turn an O(1) operation into an O(1 * x) operation, and since x is effectively constant, that would reduce to a O(1) operation). Since those shouldn't be occurring very often, that should not cause the problem described by the OP.

Andy Brummer3-Mar-11 11:40

Andy Brummer

3-Mar-11 11:40

What happens is the pages fill up and then get split as records are inserted in the middle. Splitting pages is significantly slower than just appending records on the end. It's not the O(whatever) performance, but the big ass constant performance of re-arranging data on a disk.

Curvature of the Mind now with 3D

AspDotNetDev3-Mar-11 12:02

3-Mar-11 12:02

A page is 8KB. That's not going to take long to move around. The time to split a page also averages in the long run to be O(1). Say a page holds 50 records. It may split on a single record insertion, but those other 49 record insertions would not cause a split. The average time per insertion would be O(1).

Now, if SQL sorts data within the page for each insert, that may slow things down a bit, as the entire page would essentially be rewritten each time randomly ordered data got inserted. Not sure exactly how it works at that low of a level. Even in this case, though, SQL should be able to optimize things. For example, if records are inserted in batches, adjacent records need not cause multiple sorts within a page... SQL can figure out the position within the page, and sort all the data at once before writing it to disk. And in the case that many small insertions are peformed, those individual inserts shouldn't take long enough to slow other stuff down. Of course, transaction locks and such could bork things up, but that's a different issue.