我对交易表格的存储程序有问题,用户有网络形式以几种价值找到交易。
这一进程耗时太长,我不知道如何制定适当的指数。
我储存的程序如下:
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