3

I have a table with the following schema:

AccountId: int
StartDateTime: datetime2(0)
EndDateTime: datetime2(0)

I want to be able to query this for rows with a certain AccountId, and whose date range intersects with a specified date range. E.g:

SELECT * 
FROM ... AS d
WHERE d.AccountId = @AccountId
    AND d.StartDateTime <= @EndDateTime
    AND d.EndDateTime >= @StartDateTime

This will be a very large table and I'll want to be able to query it as efficiently as possible. I've specified a single composite index of AccountId, StartDateTime, and EndDateTime.

Will this index, and the above query work effectively together? If not, what would be a better alternative? Thanks.

Barguast
  • 341
  • 2
  • 6
  • 1
    Looks like a good index to me. Load some data and look at the query plan. – paparazzo Jul 15 '15 at 18:11
  • 1
    Check with sys.dm_db_index_usage_stats to see if the index is used – Sabin B Jul 15 '15 at 18:24
  • How many rows will you typically have for each AccountId? – Martin Smith Jul 15 '15 at 19:59
  • Because you are using d.EndDateTime >= @StartDateTime then the second column in the index should be EndDateTime like this:CREATE UNIQUE NONCLUSTERED INDEX [idx_Table1_AccountID_End_Start] ON [dbo].[Table1] ( [AccountId] ASC, [EndDateTime] ASC, [StartDateTime] DESC ) – Sabin B Jul 15 '15 at 20:06
  • If AccountId is the primary key (default sql server will create a clustered index), then for the query you have specified wont even need an additional index (just having 3 columns accountid,startdateTime,enddatetime). It will do an Index seek. Also, you should not use select *, instead explicitly specify column names. – Kin Shah Jul 15 '15 at 20:59
  • 1
    You might take a look at http://dba.stackexchange.com/questions/10113/most-efficient-way-to-retrieve-date-ranges I think that your problem is similar, except you would always want accountId to precede any other index columns (assuming that you will always be querying a single accountId out of many accountIds overall) – Geoff Patterson Jul 15 '15 at 23:34

2 Answers2

2

If the intervals within a particular account do not overlap with each other, you can replace your query with a TOP (1) query, to find the last start date before your desired end date. Do the same but opposite to get your earliest end date after your desired start date. These will form a small range you can use to check.

If they do overlap within a particular account, then you have one of the classically hard problems, and I'd recommend you read the material that Dejan Sarka has written at http://solidqblogs.azurewebsites.net/en/businessanalytics/interval-queries-in-sql-server-part-1/ (and its follow ups).

The problem is that an indexes on either startdate or enddate are less than ideal, because either one alone doesn't give a clue about whether the interval reaches your desired range. Another strategy is useful instead, such as indexing values within the range (lots of storage, and you have to consider the granularity), or come up with a creative solution such as interval trees or spatial analytics.

Rob Farley
  • 16,199
  • 2
  • 38
  • 61
0

Generally, as previous comments indicate this index looks to be the correct one.

Few notes -

  • You may want to consider using a clustered index, assuming those are the typical queries you'll be running on the table
  • If the planned results are very large (say, 1m rows for an
    account/date-range) consider applying some additional filtering (for example, top x,paging etc) as even if the query/index are optimal -
    returning huge chunk of data takes its own time & resources.
0xAD
  • 21
  • 3