4

I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ million rows. I know this gets asked a lot, but I haven't found an answer that matches exactly what I'm trying to do.

The table

CREATE TABLE [dbo].[Trades]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Type] [int] NOT NULL,
    [Amount] [decimal](18, 2) NOT NULL,
    [Price] [decimal](18, 2) NOT NULL,
    [Exchange_Id] [int] NOT NULL
)

As you can see, this is a table that stores commodity trades. Because of that, there are currently 100+ million rows in the table, with about 10,000 rows being added daily (at the end of the day).

I'm at a bit of a loss as to how to index this table to maximize performance. I don't so much care about insert performance since the table is updated once a day and it doesn't need to be a fast operation. Rows are also never updated once inserted. The big problem is query speed. This table is queried relatively often.

By far the most common queries are on the [TimeStamp] column alone (eg, TimeStamp between two dates), or on the [TimeStamp] column together with the [Exchange_Id] column (eg, trades on a certain Exchange between two [TimeStamp]s).

Currently, there is a unique clustered index on the [Id] column, but I'm not sure if that's the right thing to do based on the circumstances. I've considered making the clustered index on the [TimeStamp] column, but it would have to be non-unique which I've read is generally bad. As for non-clustered indexes, I'm not quite sure how to arrange them or on what columns.

Any help is greatly appreciated.

  • Are you aware that indice work only on high selectivity (low no. of values is returned). Please post queries. Do you know how to read query planner output? There is a great free online book: use-the-index-luke.com ;) – Mladen Uzelac Nov 26 '14 at 18:22
  • @MladenUzelac Thanks for the comment! I'll check out that site. In the meantime, most of the queries involve narrow timespans of several days, so ~40,000 rows returned. There are times, however, where someone may want longer time periods in which case it's easily possible for ~500,000 records to be returned. – Amberite Nov 26 '14 at 18:24
  • Index will work if there is about 2% percent of data selected. You can always analyize query planner and see what is actually done. Good video about query planner: youtube.com/watch?v=lH2_SI04PWQ – Mladen Uzelac Nov 26 '14 at 18:28
  • @MladenUzelac Thanks, so that means that indexes should work as long as users query less than 2 million rows on this table, which is always the case. The largest queries I've seen were for ~500k rows. – Amberite Nov 26 '14 at 18:31
  • It should be. There are many videos about optimization SQL server on Youtube. And you could post some queries so you can get some advice on queries. And EXPLAIN is your friend. :D – Mladen Uzelac Nov 26 '14 at 18:54
4

Based on your question, I would index the Timestamp column with the clustered index. And to make the index unique, just make sure to include the identity column in the index definition:

... PRIMARY KEY CLUSTERED ([Timestamp], [Id])

If query performance for queries on Exchange_Id is still an issue after that, you can also add a non-clustered index that looks something like this:

UNIQUE INDEX ([Exchange_Id], [Timestamp], [Id])

.. but if you do, consider including any columns that the query may need, in order to create what's known as a covering index.

UNIQUE INDEX ([Exchange_Id], [Timestamp], [Id]) INCLUDE ([Type], [Amount], [Price])

Remember that there's a disk space issue involved as well, as you stated that your table contains a large number of rows. The clustered index will not change the amount of disk space your table consumes either way, but adding a non-clustered index will allocate extra space. If you INCLUDE all the columns from your table, like I did in the example, the non-clustered index will roughly take up as much space as the rest of the table does.

  • Thanks so much! This is exactly the kind of information I was hoping to learn. For the PRIMARY KEY CLUSTERED index, should the [TimeStamp] and [Id] columns be sorted ASC? Or will the sorting not matter? – Amberite Nov 26 '14 at 19:13
  • One more question - since we have a fixed number of Exchanges in the system, should I maybe create a new UNIQUE INDEX for each Exchange? In other words, create a UNIQUE INDEX with a WHERE [Exchange_Id] = <Id>? This should improve index speeds further, right? – Amberite Nov 26 '14 at 19:17
  • 1
    Ascending/descending won't matter on Exchange_Id and Timestamp. I would order Id ascending, though. – Daniel Hutmacher Nov 26 '14 at 19:41
  • 2
    Not sure if you would see any large benefits from creating multiple filtered indexes. What you're suggesting is an alternative to partitioning, though. Try it with the non-clustered index if you have the time and an Enterprise Edition license. – Daniel Hutmacher Nov 26 '14 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.