SQL Server 2005 proper index to filter 30,000,000 registers

Question

Hello, I have a problem with a stored procedure of a transactional table, the user have a web form to find transactions by several values.

The process is taking too long and I don't know how to set proper index.

here is my stored procedure:

CREATE PROCEDURE dbo.cg_searchTransactions
(
    @id_Ent tinyint,
    @transactionTypeID int = NULL,
    @transactionID numeric(18,0) = NULL,
    @channelID int = NULL,
    @transactionDateFrom datetime = NULL,
    @transactionDateTo datetime = NULL,
    @transactionStatusID INT = NULL,
    @documentType INT = NULL,
    @documentNumber varchar(50) = NULL,
    @userName varchar(50) = NULL,
    @accountFromNumber varchar(20) = NULL,
    @accountToNumber varchar(20) = NULL,
    @amountFrom money = NULL,
    @amountTo money = NULL,
    @correlationID varchar(30) = NULL,
    @externalReference varchar(20) = NULL,
    @externalReference2 varchar(20) = NULL,
    @PageIndex INT = 1, 
    @PageSize INT = 20
)
AS
BEGIN
    SET NOCOUNT ON


     DECLARE @QUERY VARCHAR(MAX)
     SET @QUERY = '
      WITH Trans AS (
      SELECT
       ROW_NUMBER() OVER (ORDER BY transactionID DESC) AS Row,
       T.id_Ent,
       T.transactionID,
       T.trnTypeCurrencyID,
       T.transactionDate,
       T.transactionStatusID,
       T.documentType,
       T.documentNumber,
       T.childDocumentType,
       T.childDocumentNumber,
       T.userName,
       T.accountFromNumber,
       T.accountFromType,
       T.accountFromCurrency,
       T.accountDescriptionFrom,
       T.costCenterFrom,
       T.subtotalFrom,
       T.taxamountFrom,
       T.taxamountFrom2,
       T.amountFrom,
       T.accountToNumber,
       T.accountToType,
       T.accountToCurrency,
       T.accountDescriptionTo,
       T.costCenterTo,
       T.subtotalTo,
       T.taxamountTo,
       T.taxamountTo2,
       T.amountTo,
       T.exchangeCurrency,
       T.traderAuthNumber,
       T.benefContractNumber,
       T.contractNumber,
       T.merchantID,
       T.creditCardAuthorizationNumber,
       T.comment,
       T.companyServiceCommision,
       T.usercommission,
       T.companyServiceAuthorizationNumber,
       T.customerBranchId,
       T.correlationID,
       T.transactionStartTime,
       T.transactionEndTime,
       T.enlapsedTime,
       T.serverName,
       T.externalReference,
       T.externalReference2,
       T.externalTrxType,
       T.beneficiaryName,

       C.shortName AS ChannelsShortName,
       TT.shortName AS TransactionTypesShortName,
       TS.shortName AS TransactionStatusDefShortName,
       DT.shortName AS DocumentTypesShortName,
       CDT.shortName AS ChildDocumentTypesShortName,
       AFT.shortName AS AccountTypesShortNameFrom,
       ATT.shortName AS AccountTypesShortNameTo,
       CURF.shortName AS CurrenciesShortNameFrom,
       CURT.shortName AS CurrenciesShortNameTo
      FROM
       Transactions T (NOLOCK) 

        INNER JOIN TransactionTypesCurrencies TTC
         ON T.id_Ent = TTC.id_Ent
          AND T.trnTypeCurrencyID = TTC.trnTypeCurrencyID

         INNER JOIN Channels C
          ON TTC.id_Ent = C.id_Ent
           AND TTC.channelID = C.ID

         INNER JOIN TransactionTypes TT
          ON TTC.id_Ent = TT.id_Ent
           AND TTC.transactionTypeID = TT.transactionTypeID

        INNER JOIN TransactionStatusDef TS
         ON T.id_Ent = TS.ent_Ent
          AND T.transactionStatusID = TS.ID

        INNER JOIN DocumentTypes DT
         ON T.id_Ent = DT.id_Ent
          AND T.documentType = DT.ID

        INNER JOIN DocumentTypes CDT
         ON T.id_Ent = CDT.id_Ent
          AND T.childDocumentType = CDT.ID

        INNER JOIN AccountTypes AFT
         ON T.id_Ent = AFT.id_Ent
          AND T.accountFromType = AFT.ID

        INNER JOIN AccountTypes ATT
         ON T.id_Ent = ATT.id_Ent
          AND T.accountToType = ATT.ID

        INNER JOIN Currencies CURF
         ON T.id_Ent = CURF.id_Ent
          AND T.accountFromCurrency = CURF.ID

        INNER JOIN Currencies CURT
         ON T.id_Ent = CURT.id_Ent
          AND T.accountToCurrency = CURT.ID
      WHERE 
       T.id_Ent = ' + CONVERT(VARCHAR,@id_Ent)
       IF NOT @transactionDateFrom IS NULL
        SET @QUERY = @QUERY + ' AND T.transactionDate >= ''' + CONVERT(VARCHAR,@transactionDateFrom,121) + ''''

       IF NOT @transactionDateTo IS NULL
        SET @QUERY = @QUERY + ' AND T.transactionDate <= ''' + CONVERT(VARCHAR,@transactionDateTo,121) + ''''

       IF NOT @transactionStatusID IS NULL
        SET @QUERY = @QUERY + ' AND T.transactionStatusID = ' + CONVERT(VARCHAR,@transactionStatusID)

       IF NOT @documentType IS NULL
        SET @QUERY = @QUERY + ' AND T.documentType = ' + CONVERT(VARCHAR,@documentType)

       IF NOT @userName IS NULL
        SET @QUERY = @QUERY + ' AND T.userName = ''' + @userName + ''''

       IF NOT @documentNumber IS NULL
        SET @QUERY = @QUERY + ' AND T.documentNumber = ''' + @documentNumber + ''''

       IF NOT @accountFromNumber IS NULL
        SET @QUERY = @QUERY + ' AND T.accountFromNumber = ''' + @accountFromNumber + ''''

       IF NOT @accountToNumber IS NULL
        SET @QUERY = @QUERY + ' AND T.accountToNumber = ''' + @accountToNumber + ''''

       IF NOT @amountFrom IS NULL
        SET @QUERY = @QUERY + ' AND T.amountTo >= ' + CONVERT(VARCHAR,@amountFrom)

       IF NOT @amountTo IS NULL
        SET @QUERY = @QUERY + ' AND T.amountTo <= ' + CONVERT(VARCHAR,@amountTo)

       IF NOT @correlationID IS NULL
        SET @QUERY = @QUERY + ' AND T.correlationID = ''' + @correlationID + ''''

       IF NOT @externalReference IS NULL
        SET @QUERY = @QUERY + ' AND T.externalReference = ''' + @externalReference + ''''

       IF NOT @externalReference2 IS NULL
        SET @QUERY = @QUERY + ' AND T.externalReference2 = ''' + @externalReference2 + ''''

       IF NOT @channelID IS NULL
        SET @QUERY = @QUERY + ' AND C.ID = ' + CONVERT(VARCHAR,@channelID)

       IF NOT @transactionTypeID IS NULL
        SET @QUERY = @QUERY + ' AND TT.transactionTypeID = ' + CONVERT(VARCHAR,@transactionTypeID)

      SET @QUERY = @QUERY + ')'
      SET @QUERY = @QUERY + 'SELECT * FROM Trans WHERE Row BETWEEN (' + CONVERT(VARCHAR,@PageIndex) + ' - 1) * ' + CONVERT(VARCHAR,@PageSize) + ' + 1 AND ' + CONVERT(VARCHAR,@PageIndex) + '*' + CONVERT(VARCHAR,@PageSize)

      SET @QUERY = @QUERY + 'OPTION (FAST 1)'

      EXEC(@QUERY)

END

Answer 1

You just need to create the separate indexes on all fields used in the WHERE clause, that is transactionDate, transactionStatusID etc. If you have a id_ent as an additional filter, include it as a leading column:

CREATE INDEX ix_transaction_transactionDate ON transaction (id_ent, transactionDate)
CREATE INDEX ix_transaction_transactionStatusID ON transaction (id_ent, transactionStatusID)
-- etc.

Note that for each query only one index will be used, and SQL Server will try to select the most appropriate one (the most selective one).

Also note that placing a NOLOCK hint on a production table is a very bad idea. You can have dirty reads within a single query.

If the id_ent is a part of a PRIMARY KEY in all of your tables, you better replace it with the constant. This query:

SELECT  *
FROM    Transactions t
JOIN    TransactionTypesCurrencies ttc
ON      ttc.trnTypeCurrencyID = t.trnTypeCurrencyID 
WHERE   t.id_ent = @id_ent
        AND ttc.id_ent = @id_ent

is generally better than this one:

SELECT  *
FROM    Transactions t
JOIN    TransactionTypesCurrencies ttc
ON      ttc.id_ent = t.id_ent
        AND ttc.trnTypeCurrencyID = t.trnTypeCurrencyID 
WHERE   t.id_ent = @id_ent

, since the early filtering can be done.

This makes no difference if you have only a single value of id_ent, but will pay for itself if you will ever add another one.

Update:

If you have the recurring queries that filter on more than one conditions and are slow, you can consider creating additional composite indexes on several conditions.

See this article in my blog for some suggestions on how to do this:

• Creating indexes

Answer 2

You can get some empirical information by using the profiler to record a workload and then use the index tuning wizard with that workload to determine the indexes best able to handle the workload.

The more indexes you create, the more work that inserts will have to do, so creating indexes on everything being searched on might not be a good idea.

Answer 3

I found that in this scenario it can be quicker to create a temp table rather than a common table expression. This also allows you to return the total number for paging.

Answer 4

Questions which I ask myself before I create an index:

1. Is this table(or tables) going to be read only or read write ?

ReadWrite - everytime there is an update/delete to the table, the index would be updated.it might be fast for "select" but slow for insert,update and delete. To quote MS "If you have a large number of indexes on a table, you increase the chance that the optimizer will choose a suboptimal index for a query plan."

1. You are creating queries on the fly.What I would do is try to run trace on QA (or production DB for a specific time) and see what the users are trying to run. You can get a dump of the DB from production to your sandbox/ try tools like index tuning wizard (which can tell you what indexes are needed) , SQL DMVs etc to find where the bottleneck is. The problem need not be only with this SP, but there might be deadlocks, improper use of temp tables/ temp DB etc.

2. If you are reasonably confident that this table is the culprit, you should also try partitioning the table horizontally

3. When you execute any query, look at the execution plan and look for table scans- which usually means that some index is missing

4. Read, Read and Read.