[Solved] Using clustered than non-clustered index with columns that include date and nvarchar(50)
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Using clustered than non-clustered index with columns that include date and nvarchar(50)

I have a table called 'GameTransactions'. It is critical for the table to work well in terms of performance (The table will have millions of records when the site is going to be operational). I thought to index it. The columns that I used for the columns are:

UserID [int],
TransactionID [nvarchar(50)]
ProviderID [int]
TransactionTimeStamp [datetime]

Some context on how I use the table.

At the begining of the SQL operation I check if the transaction ID exists for the same user. 

   SELECT COUNT(1) 
    FROM GameTransactions WITH(NOLOCK)
    WHERE 
    [email protected] AND
    [email protected] 
    AND [email protected] 
    AND TransactionTimeStamp>DATEADD(MONTH,-1,GETUTCDATE())

If the request doesnt already exist in the database, I insert it.

I chose to use the following index

CREATE CLUSTERED INDEX IX_GameTransactions_UserID_TransactionID_ProviderID_TransactionTimeStamp
ON dbo.GameTransactions (UserID,TransactionID,ProviderID,TransactionTimeStamp);

I read in this article:

https://sqlstudies.com/2014/12/01/using-a-date-or-int-column-as-the-clustered-index/

That it is possible to achieve good performance with datetime being a column in a clustered index. I dont care about disk space that the clustered index is going to take, I am more concerned about speed performance.

I also thought about an alternative solution, 

 CREATE NONCLUSTERED INDEX IX_GameTransactions_UserID_TransactionID_ProviderID_TransactionTimeStamp
 ON dbo.GameTransactions (UserID, Month, Year,ProviderID)
 INCLUDE (TransactionID);

I could add 2 additional columns - Month and year. And work with ints instead of date. Keep in mind that the 'TransactionID' field has to be an nvarchar(50). There is no way to work around it.

I have an additional Id column which is auto-incrementing. Would such a solution work?

  CONSTRAINT PK_GameTransactions PRIMARY KEY CLUSTERED (
      UserID
    , TransactionID
    , ProviderID
    , TransactionTimeStamp
, Id
)

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `GameTransactions` ADD INDEX `gametransactions_idx_userid_transac_provide_transac` (`UserID`,`TransactionID`,`ProviderID`,`TransactionTimeStamp`);
The optimized query:
SELECT
        COUNT(1) 
    FROM
        GameTransactions WITH (NOLOCK) 
    WHERE
        GameTransactions.UserID = @UserID 
        AND GameTransactions.TransactionID = @TransactionID 
        AND GameTransactions.ProviderID = @ProviderID 
        AND GameTransactions.TransactionTimeStamp > DATEADD(MONTH, -1, GETUTCDATE())

Related Articles



* original question posted on StackOverflow here.