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:
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 )
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `GameTransactions` ADD INDEX `gametransactions_idx_userid_transac_provide_transac` (`UserID`,`TransactionID`,`ProviderID`,`TransactionTimeStamp`);
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())