0

Database is SQLAzure

I've a recent requirement to store incremental updates in status against an Order item, whose primary key is a GUID (stored as an nvarchar(36) :/ ). An OrderStatusLog entry might happen at any time, it would have minimally the OrderId, ChangeDateTime, and the new Status.

There may be hundreds of status updates per order (but realistically, probably only tens) and they're all kept for audit reasons so that at any point the questions "what were the status changes over time?" and "what was the order status at time X?" can be answerd.

It will also be queried, potentially several hundred times (but again, most likely tens), what the current (i.e the most recent row) status is. Generally the chances that a recent Order will be queried are much higher than an old order, which is where I think the Order Id being a GUID might be a nuisance, because there isn't an innate order to them (nor can I change them to be sequential) so status change records end up scattered all over the storage regardless of when they were created

I'm not so interested to know the date the change occurred for the frequent question of "what is the most recent status?" and auto numbering doesn't have to start with 1 for every different order, so the Revision column could just be a single auto inc int and it could answer the "what is the most recent row for OrderId X?". For the infrequent question of "what were all the changes in order?" an autoinc answers it too, and for or "what was the status on date X?" the date is useful, but this will be seldom asked

Does the OrderId being a GUID present a problem no matter what? Is it still worth creating a table that has a clustering index of OrderId (guid) and e.g. Revision (an auto incrementing int) as its key columns (in that order) - is it the ideal arrangement for a historical log of status changes such as this? Or should I not be trying to influence storage order, and instead be saying "well, the likely number of change records will be small, and retrieving and sorting them all every time they're needed is likely fairly inconsequential" and just nonclustering the OrderId and including the date(or revision) and status?

Note; I can't put the current status on the Order table alas (not my restriction)

Edit: some extra info on the tables:

Order                        --stores orders
Id NVARCHAR(36) PRIMARY KEY, --guid stored as string (yes.. :/ )
... other fields

OrderStatusLog               --tracks all the changes an order went through. An INSERT-only (and SELECT) table
OrderId UNIQUEIDENTIFIER,    --references Oder(id) but not an FK officially because of the type mismatch
ChangeDate DATETIME,         --the time of the change in status
Status                       --the status as of time ChangeDate

I'll frequently want to (100s of times a day):

SELECT TOP 1 Status FROM OrderStatusLog WHERE OrderId = @id ORDER BY ChangeDate DESC

Infrequently (10s a day):

SELECT TOP 1 Status FROM OrderStatusLog WHERE OrderId = @id AND ChangeDate < @d ORDER BY ChangeDate DESC

Seldom (1 a day):

SELECT * FROM OrderStatusLog WHERE OrderId = @id ORDER BY ChangeDate DESC

99% of the time these queries would be operating on rows inserted within the past 24 hours.


I'm trying to decide how to index this log table given that it has a guid. My understanding of indexing in SQLS is that a clustered index influences the order of storage of rows on disk, and using GUIDs causes a bit of a nuisance with reoganizing because a GUID may naturally want to sit in the middle of existing data, meaning that data pages end up splitting. I'd like to know the implications of resource use of clustering vs nonclustering; if one indexing strategy means that inserts are more costly as (on average) they cause more data to be shuffled around disk but it makes selecting the data faster/lower cost, versus another strategy wher the insert might be cheap but the cost of selecting be much higher.

At this time the use case is just for those shown; no other uses are planned

This log table doesn't have, or seemingly need, a primary key and though infeasible that two updates would happen in the same millisecond (or whatever) as a more generic solution I'd like to know how to control for situations where updates could come in the same millisecond (i.e. I'm thinking an autoinc int column could help satisfy ORDER BY x DESC for "most recent" and then the "could be simultaneous" is moot)


As I've often advised others, I've looked at doing some benching of this. I created 3 tables of identical columns, made:

  • one a clustered on OrderId+ChangeDate+Status,
  • one a nonclustered unique on OrderId+ChangeDate include Status, and
  • one a nonclustered nonunique OrderId include ChangeDate+Status

I filled each table with the same set of 100,000 records, of approx 10,000 distinct guids and ~10 status changes over a random time frame. Table load performance was roughly the same - about 33 seconds on my local SQL Express from a C# app.

I ran 100,000 iterations of the frequent query against each table, hitting every Guid in each table 10 times with runtimes of 23 seconds (clustered), 22 seconds (nonclustered OI-CD include S) and 28 seconds (nonclustered OI include CD-S).

Index and table storage stats:

enter image description here

enter image description here

After swapping the clustered unique index on all table cols out for a clustered nonunique on OrderId+ChangeDate, and swapping the nonclustered unique on OI+CD out to be unique (and truncating all tables, backing up db, then re-loading it)

enter image description here

And plans:

enter image description here

enter image description here

enter image description here

2 Answers 2

1

Slightly different (but probably materially the same) suggestion from Akina would be to index on (OrderId, ChangeDateTime) INCLUDE (Status) since you're only selecting Status never filtering on it directly. This would service queries of the kind:

SELECT TOP 1 status
FROM sourcetable
WHERE orderid = @GUID
-- AND ChangeDateTime <= @ChangeDateTime
ORDER BY ChangeDateTime DESC

GUIDs are obviously not the most ideal column to index on because of their data size, and more so because of their randomness resulting in higher fragmentation, but if it's all you have to work with then it's better to index it than not. You'll still see improved query performance with the index on it.

There's probably going to be no difference by adding an INY IDENTITY column to use in your index vs using the existing DATETIME field, as they both take up the same storage anyway.

There's also not much inherent difference between CLUSTERED and NONCLUSTERED indexing in your case, but if you have the luxury, and your examples are the most common type of querying that will be done, then go with the CLUSTERED index as that will provide you greater flexibility in the future with minimizing performance hits should you end up needing to SELECT other fields.

The exact index recommendation in that case would then be CREATE UNIQUE CLUSTERED INDEX IX_IndexName ON TableName (OrderId, ChangeDateTime). You're able to leverage the UNIQUE clause here since your GUID guarantees it, which can slightly help performance of the index as well.

10
  • There isn't any doubt in my mind that I will index it.. I'm trying to know more about the best type of indexing (and by consequence, table design) strategy.. i.e. cluster or not, what columns to index (whether to add an auto int revision and index that or just use the datetime), and what to include
    – Caius Jard
    Mar 16, 2021 at 12:17
  • @CaiusJard I updated my answer with some additional information regarding your questions.
    – J.D.
    Mar 16, 2021 at 12:26
  • Though I'm not certain that the data is automatically unique on guid+time I could look to make it a business rule that an order doesn't change status more than once a millisecond etc :)
    – Caius Jard
    Mar 16, 2021 at 12:31
  • I don't have an in-depth knowledge of how SQLS stores data, which is one of the drivers for this question. In terms of cluster indexing a guid, is it not the case that they cause problems as tables grow because of the equal likelihood that data will need to be inserted "into the middle of the table" (for want of a better description of them filling up/causing splits in pages all over the place rather than a sequential key that concentrates recent rows in fewer pages) because the table data is kept ordered by something that has no inherent order? How do we balance the cost of keeping data in ..
    – Caius Jard
    Mar 16, 2021 at 12:39
  • .. order versus the cost of having to retrieve it from all over the place and order it every time we want to use it? Is an index on fewer keys, but that includes more data cheaper to use in this regard?
    – Caius Jard
    Mar 16, 2021 at 12:41
0

The most obvious improvement is the creation of an index by (OrderId, ChangeDateTime, Status). If your query looks like

SELECT TOP (1) status
FROM sourcetable
WHERE orderid = @GUID
-- AND ChangeDateTime <= @ChangeDateTime
ORDER BY ChangeDateTime DESC

then it must be fast enough - the index is covering, so the on-disk table body access not needed at all.

6
  • Given the question is about SQL Server. SELECT TOP (1) instead of LIMIT 1 is the needed construct.
    – Dan Guzman
    Mar 16, 2021 at 12:00
  • But the question is more about the kind of index we should create.. Clustering on OrderId and Revision with Status, or nonclustering on OrderId with Revision and Status
    – Caius Jard
    Mar 16, 2021 at 12:04
  • I'm trying to understand how guids affect clustering when theyre part of a compound index in a scenario where we're striving to know the most recent row for a given GUID, and if they cause a detriment then how we can work around it.. And if there is a sense to having the other part of a compound cluster be an autoinc int
    – Caius Jard
    Mar 16, 2021 at 12:10
  • @DanGuzman Given the question is about SQL Server. This affects correct syntax only. the question is more about the kind of index we should create Nonclustering index is more compact. And so it seems to be preferred because it is covering one.
    – Akina
    Mar 16, 2021 at 12:36
  • @Akina, I don't disagree the question is about indexing, only pointing out the incorrect query syntax in your answer that may be confusing to others in a SQL Server context.
    – Dan Guzman
    Mar 16, 2021 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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