1

I have scenario where the data will be loaded pretty much in the order it will be grouped by in reports (date, varchar foreign key, int foreign key). Is there still a benefit to indexing? I was going to define the run date as the clustered index, and index the two foreign keys. Since the data will be loaded via a process that will already have the data sorted, I wasn't sure if the benefit to the indexes still outweigh the overhead? I am really new at this. The table will probably be defined with a total of 4 fields, a max of 22,000,000 rows per run date, and retained for 60 days.

Improve this question
3
  • 4
    You should refer to data loading performance guide. Loading data ordered by Clustered index is a good thing which will prevent index fragmetation, but there are many things that you can tune while loading data. This is a broad topic to get answered on this site. I have provided you a good pointer that will help you.
    – Kin Shah
    Commented Nov 5, 2015 at 18:28
  • It's also important to not that if you're ordering by what would be the clustered index anyway there should be very little overhead.
    – Zane
    Commented Nov 5, 2015 at 20:19
  • 2
    You still want a clustered index so that the engine will KNOW that the data is in the correct order. Otherwise it's just a guess for the engine. Create the clustered index on the date field.
    – Jonathan Fite
    Commented Nov 5, 2015 at 20:29

1 Answer 1

Reset to default
1

This is a complicated topic, I will try to be brief.

Benefit: Performance boost if implemented correctly

Drawback: requires understanding of clustered/nonclustered indexes and storage implications

Heaps (tables without clustered indexes) are fine for loading but can lead to poorly performing queries especially when dealing with millions of records as in your case. Even though you sorted your data prior to inserting, SQL Server does not know this and will simply put new records wherever there is room.
If there are not even any non-clustered indexes, queries against the heap will result in full table scans.

Assuming you are pre-sorting the data by datetime, and assuming that your date column is datetime, clustering on this column will result in extremely fast queries as long as they filter against the date. There are drawbacks, however, one of which being SQL Server will addding a 4 byte uniquifier to the index. Also any additional indexes defined will use the clustered index as the key, so you are in effect adding 12 bytes (8 bytes for datetime + 4 bytes for uniquifier) to the storage requirements for every value in the index.

I would add synthetic key instead. Make it a self incrementing integer value. Make it the primary key of the table and SQL Server will by default add a clustered index to that column. No uniquifier will be needed, so additional indexes will only require 4 bytes of storage for the key.

Note: varchar foreign keys can lead to poor performance as well. Change the base table to have an integer primary key instead.

Improve this answer
5
  • 2
    I couldn't disagree more on the auto incrementing integer as the Primary key clustered index. If the value will never be called again than what's the point of wasting your clustered index on it? You gain a lot of power out of being able to search through that clustered index.
    – Zane
    Commented Nov 5, 2015 at 21:04
  • In high performance situations, every byte counts. If you are willing to trade off performance for ease of querying, then sure. How many indexes will be on the table? How many rows? The numbers add up quickly. I keep my indexes as narrow as possible so more entries fit on the page, making scanning much faster. Have you ever dealt with VLDB's? You can still use the synthetic key in queries, it just takes a little more thought.
    – datagod
    Commented Nov 5, 2015 at 21:23
  • 1
    Yeah but in high performance situations a Clustered index can be your best friend. You can gain an large amount of querying performance by having your clustered index on the natural key. In a lot of tables that's where most of your index usage to begin with. I'm not saying there aren't scenarios where you approach isn't ideal but it's certainly not always the answer.
    – Zane
    Commented Nov 5, 2015 at 21:47
  • 1
    Not to mention then you either have to make your other indexes either covering or you're going to have to do a ton of key look ups and those aren't cheap either.
    – Zane
    Commented Nov 5, 2015 at 21:49
  • @zane, you are right there for sure. There is rarely a clear-cut answer. Every scenario is different. Sometimes performance tuning is more of an art than a science.
    – datagod
    Commented Nov 6, 2015 at 15:10

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