0

This seems like a generic problem that should have been solved already, but I can't find anything about this. In general this question is - given a table where data is read by a date range, what is the best, most efficient setup?

We have a calendar event table that will quickly grow to millions of records.

The schema is something like:

CREATE TABLE [dbo].[CalendarEvent](
[Id] [uniqueidentifier] NOT NULL,
[DtStart] [datetime] NULL,
[DtEnd] [datetime] NULL,
[Created] [datetime] NULL,
[LastModified] [datetime] NULL,
[CalendarEventType] [nvarchar](255) NULL,
[CalendarId] [uniqueidentifier] NULL
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Forget about recurring events, etc. as that doesn't bear on our problem.

Most queries will be of the type:

select * from CalendarEvent where CalendarId = 'b5d6338f-805f-4717-9c0a-4600f95ac515' AND dtStart > '01/01/2020' AND dtStart < '10/22/2020'

Notice no joins, etc.

But we will also have some that select for individual events, and include joins:

select * from CalendarEvent ce join tags t on ce.Id = t.CalendarEventId where Id = '17606330-5486-496a-a91c-f5d0e123bfff'

Questions and ideas:

  1. Should we keep the Id as the PK, but make the start date the clustered index?
  2. Should we just make an index on dtStart?
  3. Should we partition by month?
  4. Should we denormalize a little and break duplicate the dtStart data by include year and month columns that we can index and use in our range queries?

In general, when you do your querying on a table by date range, what is the best setup for this type of table?

Note: If you think this question could be improved to help more people, make it more generic and widely applicable, such as removing references to a Calendar Event table specifically, and making this just about date range querying in any type of table, please help me do that.

0

So there's probably a few different things you can try but what stands out to me from your schema and examples are the following:

  1. Avoid using UniqueIdentifiers if possible, and try to use Int or BigInt instead. (Int has over 8 billion values, BigInt over 18 quintillion.) It'll definitely make a difference in indexing performance.

  2. Create a unique clustered index on your PK field (which it looks like you're already doing, so you can keep it as is).

  3. Create a nonclustered index on CalendarId and dtStart.

  4. Avoid SELECT * in your queries, unless you're using every column every time, it's faster to only select the exact columns you need (and either way it's best practice to list your columns out instead of using * for reliability reasons).

*5. If you're able to do #4 then when you create the nonclustered index in #3, you can use the INCLUDE keyword to specify names of the columns you'll be normally selecting. This eliminates key lookups and will be a little more performant.

  1. You can look into creating an indexed view on top of:

    select * from CalendarEvent ce join tags t on ce.Id = t.CalendarEventId

You just have to replace * with the actual columns you'll be using, and you need a unique value (likely the primary key of one of the tables) that you can create a unique clustered index on. This will basically store the data of this view as if it was a table and can save you some time. You also get the added benefit of being able to create additional nonclustered indexes on it if necessary as well.

This Brent Ozar article is also useful to be aware of for the limitations of Indexed Views: What You Can (and Can’t) Do With Indexed Views

  1. You can also look into Table / Index Compression but this will be dependent on how much data is in your tables / indexes and if the bigger hardware bottleneck is I/O vs CPU.
| improve this answer | |
  • 1
    Thanks @J.D., good advice. I can't change the PK because we already have a lot of production data. But the others are good advice. Anything else you can think of? What about partitioning the table? – richard Nov 19 at 12:09
  • Table partitioning isn't intended nor really useful for performance. Compression on the other hand may help your performance depending on if your hardware bottleneck is moreso disk than CPU (e.g. if you're using slow disks but have very good and underused CPUs, then compression might be beneficial to you). But the index suggestions I provided should make the most difference. Also you could create an indexed view for your second query example to materialize the data ahead of time (I'm updating my answer now with how to do that). – J.D. Nov 19 at 12:57
  • Thanks JD. I'm in azure, so limited on options. What compression do you refer to? Is that a setting in SQL Server? – richard Nov 20 at 23:00
  • 1
    @richard I updated my answer to link to Table / Index compression. I would focus on the other things first though. Fortunately they're all supported (compression too) in Azure. – J.D. Nov 20 at 23:35

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.