The below procedure taking 9 seconds to execute, and the primary table has only 30000 records.
ALTER PROC [dbo].[TransactionReport_NewWork] @StartDate datetime = NULL, @EndDate datetime = NULL
, @Mid varchar(50) = NULL -- varchar(5000)=null
, @BatchNo varchar(50) = NULL,
@AuthId varchar(50) = NULL, @RRN varchar(50) = NULL --varchar(500)=null
, @CardNo varchar(50) = NULL, @PageNo int = NULL, @PageSize int = 10, @ReportType int = NULL, @ReportSubType int = NULL,
@IsOrder varchar(10) = NULL,
@CurrencyCode varchar(10) = NULL
, @InvoiceNo varchar(50) = NULL
, @AppCode varchar(5) = NULL
, @ProductCodes StringValues READONLY, --varchar(10)=null,
@TIDS StringValues READONLY
, @SettlementStatus IntegerValues READONLY -- varchar(50)=null
, @TransactionType IntegerValues READONLY
, @ReportValues StringValues READONLY
AS
SET @PageNo = (CASE
WHEN @PageNo = 0 THEN 1
WHEN @PageNo IS NULL THEN 1
ELSE @PageNo
END)
SET @PageSize = (CASE
WHEN @PageSize = 0 THEN 2147483647
ELSE @PageSize
END)
SELECT
x.* --into #TransLogDetail
FROM (SELECT TOP 100 PERCENT
ROW_NUMBER() OVER (
--order by a.TransactionDateTime desc
ORDER BY
CASE
WHEN j.ProductName IS NOT NULL THEN j.ProductCode
END ASC,
CASE
WHEN j.ProductName IS NULL THEN a.TransactionDateTime
END DESC,
a.TransactionDateTime DESC
) SNo,
b.MerchantName,
a.var64_42 MID,
a.var64_41 TID,
(CASE
WHEN c.TransactionType = 'SALE' THEN d.TransactionStatus
WHEN c.TransactionType = 'MOTO' THEN 'MOTO'
--when c.TransactionType ='LOYALTY_POINTS_REDEMPTION' then c.TransactionType
ELSE d.TransactionStatus
END)
AS TransactionType,
a.var64_60 BatchNo,
(CASE
WHEN
a.var64_54 IS NOT NULL THEN CAST(CONVERT(decimal, a.var64_04) / 100 AS numeric(18, 2)) - CAST(ISNULL(CONVERT(decimal, a.var64_54) / 100, 0) AS numeric(18, 2))
ELSE CAST(CONVERT(decimal, a.var64_04) / 100 AS numeric(18, 2))
END) Amount,
(CASE
WHEN c.TransactionType = 'MOTO' THEN SUBSTRING(a.var64_02, 1, 6) + '******' + SUBSTRING(a.var64_02, 13, 4)
ELSE
--substring(a.var64_35,1,6) +'******'+ substring(a.var64_35,13,4)
SUBSTRING(a.var64_02, 1, 6) + '******' + SUBSTRING(a.var64_02, 13, 4)
END)
CardNumber,
a.var64_38 AuthID,
a.var64_37 RRN,
a.var64_62 InvoiceNo,
CONVERT(datetime, STUFF(STUFF((CONVERT(varchar, DATEPART(YEAR, GETDATE())) + a.var64_13 + ' ' + a.var64_12), 12, 0, ':'), 15, 0, ':'))
TransactionDateTime,
e.SettlementStatus,
a.var64_35 Track2,
a.var64_11 Stan,
h.Description AS POSEntryMode,
g.Description AS POSConditionCode,
(CASE
WHEN a.var64_54 IS NULL THEN NULL
ELSE CAST(ISNULL(CONVERT(decimal, a.var64_54) / 100, 0) AS numeric(18, 2))
END) Tip,
a.var64_55 BatchData,
a.OrderNo,
a.var64_48 KSN
--, convert(varchar,a.TransactionDateTime,120) TransactionDateTime_Web
,
STUFF(STUFF((CONVERT(varchar, DATEPART(YEAR, GETDATE())) + a.var64_13 + ' ' + a.var64_12), 12, 0, ':'), 15, 0, ':') TransactionDateTime_Web
--, isnull( i.Code +'('+isnull(i.Symbol,'')+')', N'PKR(?)') as CurrencyCode
,
ISNULL(i.Code, 'PKR') AS CurrencyCode,
a.var64_02 Pan,
j.ProductName,
(CASE
WHEN a.var64_28 = '' THEN 0
ELSE CAST(ISNULL(CONVERT(decimal, a.var64_28) / 100, 0) AS numeric(18, 2))
END) AS ProductPrice,
(CASE
WHEN a.var64_28 = '' THEN 0
ELSE CAST(ISNULL(CONVERT(decimal, RIGHT(a.var64_61, 12)) / 100, 0) AS numeric(18, 2))
END) ProductQuantity,
--a.var64_28 as ProductPrice
--,a.var64_61 as ProductQuantity,
(CASE
WHEN LEN(a.var64_63) >= 57 THEN CAST(ISNULL(CONVERT(decimal, SUBSTRING(a.var64_63, 1, 12)) / 100, 0) AS numeric(18, 2)) +
CAST(ISNULL(CONVERT(decimal, SUBSTRING(a.var64_63, 43, 12)) / 100, 0) AS numeric(18, 2))
ELSE 0
END) TotalDiscount,
TotalRecords = COUNT(*) OVER (),
TotalPages = CAST(CEILING(COUNT(*) OVER () / (@PageSize * 1.0)) AS int)
--substring(a.var64_63,1,12)+'Part2'+substring(a.var64_63,43,12) TotalDiscount
FROM TransactionResponseLog a
LEFT JOIN Merchant b
ON a.var64_42 = b.mid
AND b.isactive = 1
LEFT JOIN GatewayTransactionType c
ON a.TransactionTypeID = c.TransactionTypeID
LEFT JOIN TransactionStatus d
ON a.TransactionStatusID = d.TransactionStatusId
LEFT JOIN SettlementStatus e
ON a.SettlementStatusID = e.SettlementStatusId
LEFT JOIN Association f
ON
--substring(a.var64_35,1,1)
SUBSTRING(a.var64_02, 1, 1)
= f.PaymentAssocationCode
LEFT JOIN POSConditionCode g
ON a.var64_25 = g.Code
LEFT JOIN POSEntryMode h
ON a.var64_22 = h.Code
LEFT JOIN CurrencyCode i
ON i.IsoCode = ISNULL(a.var64_49, '0586')
LEFT JOIN ProuctWithRequestId j
ON a.TransRequestID = j.TransRequestID
WHERE a.var64_42 = ISNULL(@MID, a.var64_42)
--and a.var64_49 =isnull(@CurrencyCode,a.var64_49)
AND
(
-------------For Currency Check
------- For Currency Check Is Not Null
((
--a.var64_49 =@CurrencyCode
i.IsoCode = @CurrencyCode
)
AND (@CurrencyCode IS NOT NULL))
OR
----For All Transactions
((@CurrencyCode IS NULL)
AND (1 = 1))
)
AND (
-------------For InvoiceNo Check
--select * from TerminalSequence
------- For InvoiceNo Check Is Not Null
((a.var64_62 = @InvoiceNo)
AND (@InvoiceNo IS NOT NULL)
AND a.var64_60 = (SELECT
dbo.fn_LPAD(a.BatchNo, 6, '0')
FROM TerminalSequence a
WHERE a.TID IN (SELECT
*
FROM @ReportValues)
AND a.AppCode = @AppCode)
)
OR
----For All Transactions
((@InvoiceNo IS NULL)
)
)
AND (
((j.ProductCode IN (SELECT
*
FROM @ProductCodes)
)
AND (@ProductCodesCount <> 0))
OR ((@ProductCodesCount = 0))
)
AND (
((a.var64_41 IN (SELECT
*
FROM @TIDS)
)
AND (@TIdCount <> 0))
OR ((a.var64_41 = a.var64_41)
AND (@TIdCount = 0))
)
AND (
------- For Gateway Transactions Other Than Moto
((@TranTypeExMotoCount <> 0)
AND (a.TransactionStatusId IN (SELECT
*
FROM @TransactionTypeExMoto)
)
AND (a.TransactionTypeId <> @MotoId-- ( select * from @TransactionTypeMoto )
)
)
OR
------- For Gateway Moto Transaction
((@TranTypeMotoCount <> 0)
AND (a.TransactionTypeId = @MotoId))
--- For All Transactions
OR
((a.TransactionStatusId = a.TransactionStatusId)
AND (@TranTypeCount = 0))
)
AND CONVERT(date, a.TransactionDateTime) BETWEEN ISNULL(CONVERT(date, @StartDate), a.TransactionDateTime)
AND ISNULL(CONVERT(date, @EndDate), a.TransactionDateTime)
AND (
((a.SettlementStatusId IN (SELECT
*
FROM @SettlementStatus)
)
AND (@SettlementStatusCount <> 0))
OR ((a.SettlementStatusId = a.SettlementStatusId)
AND (@SettlementStatusCount = 0))
)
AND a.var64_38 = ISNULL(@AuthID, a.var64_38)
AND a.var64_37 = ISNULL(@RRN, a.var64_37)
--and substring(a.var64_35,1,16) =isnull(@CardNo,substring(a.var64_35,1,16))
AND
(
(
(c.TransactionType IN ('SALE', 'REFUND', 'SETTLEMENT', 'LOYALTY_POINTS_REDEMPTION'))
AND
--( substring(a.var64_35,1,16) =isnull(@CardNo,substring(a.var64_35,1,16)) )
(a.var64_02 = ISNULL(@CardNo, a.var64_02)
)
)
OR ((c.TransactionType = 'MOTO')
AND (a.var64_02 = ISNULL(@CardNo, a.var64_02)))
)
--and a. = isnull(@BatchNo,a.var64_60)
AND
(
-------------For BatchNo Check
------- For BatchNo Check Is Not Null
((a.var64_60 = @BatchNo)
AND (@BatchNo IS NOT NULL))
OR
----For BatchNo Check Is Null
((@BatchNo IS NULL)
AND (1 = 1)))
AND a.var64_39 = '00'
AND c.TransactionType IN ('SALE', 'MOTO', 'PUSH_QR_SALE', 'REFUND', 'SETTLEMENT', 'LOYALTY_POINTS_REDEMPTION')
AND (
-------------For Transactions Based On Order
------- For Is Order 1 Get Only Order Transactions
((@IsOrder = 1)
AND (a.OrderNo IS NOT NULL))
------- For Is Order 0 Get Other Transactions Than Order Transactions
OR
((@IsOrder = 0)
AND (a.OrderNo IS NULL))
OR
----For All Transactions
((@IsOrder IS NULL)
AND (1 = 1))
)
AND
----Start Of First And
(
(
@ReportTypeVar = 'TO_DATE'
AND (
((CONVERT(varchar, a.TransactionDateTime, 106) IN (SELECT
*
FROM @ReportValues)
)
AND (@ReportValCount <> 0))
OR ((CONVERT(varchar, a.TransactionDateTime, 106) = CONVERT(varchar, a.TransactionDateTime, 106))
AND (@ReportValCount = 0))
)
)
OR (
@ReportTypeVar = 'WEEKDAY'
AND (
((DATENAME(WEEKDAY, a.TransactionDateTime) IN (SELECT
*
FROM @ReportValues)
)
AND (@ReportValCount <> 0))
OR ((DATENAME(WEEKDAY, a.TransactionDateTime) = DATENAME(WEEKDAY, a.TransactionDateTime))
AND (@ReportValCount = 0))
)
)
OR (
@ReportTypeVar = 'MONTH'
AND (
((DATENAME(MONTH, a.TransactionDateTime) IN (SELECT
*
FROM @ReportValues)
)
AND (@ReportValCount <> 0))
OR ((DATENAME(MONTH, a.TransactionDateTime) = DATENAME(MONTH, a.TransactionDateTime))
AND (@ReportValCount = 0))
)
)
OR (
@ReportTypeVar = 'QUARTER'
AND (
((DATENAME(QUARTER, a.TransactionDateTime) IN (SELECT
*
FROM @ReportValues)
)
AND (@ReportValCount <> 0))
OR ((DATENAME(QUARTER, a.TransactionDateTime) = DATENAME(QUARTER, a.TransactionDateTime))
AND (@ReportValCount = 0))
)
)
OR (
@ReportTypeVar = 'DayOfMonth'
AND (
((DATENAME(DAY, a.TransactionDateTime) IN (SELECT
*
FROM @ReportValues)
)
AND (@ReportValCount <> 0))
OR ((DATENAME(DAY, a.TransactionDateTime) = DATENAME(DAY, a.TransactionDateTime))
AND (@ReportValCount = 0))
)
)
OR (
@ReportTypeVar = 'TID_WISE'
AND (
((a.var64_41 IN (SELECT
*
FROM @ReportValues)
)
AND (@ReportValCount <> 0))
OR ((a.var64_41 = a.var64_41)
AND (@ReportValCount = 0))
)
)
OR (
@ReportTypeVar = 'BATCHNO'
AND (
((a.var64_60 IN (SELECT
*
FROM @ReportValues)
)
AND (@ReportValCount <> 0))
OR ((1 = 1)
AND (@ReportValCount = 0))
)
)
OR (
@ReportTypeVar = 'ASSOCIATION'
AND (
((f.AssociationName IN (SELECT
*
FROM @ReportValues)
)
AND (@ReportValCount <> 0))
OR ((f.AssociationName = f.AssociationName)
AND (@ReportValCount = 0))
)
)
)
----End Of First And
ORDER BY CASE
WHEN j.ProductName IS NOT NULL THEN j.ProductCode
END ASC,
CASE
WHEN j.ProductName IS NULL THEN a.TransactionDateTime
END DESC, a.TransactionDateTime DESC) x
ORDER BY x.sno ASC
OFFSET ((@PageNo - 1) * @PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY;
**The result of execution plan is below.**
Table 'Worktable'. Scan count 3, logical reads 55959, physical reads 0, read-ahead reads 1158, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TransactionRequestLog'. Scan count 1, logical reads 2979, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProductHeirarchy'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'POSConditionCode'. Scan count 1, logical reads 19452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#B1653DF7'. Scan count 1, logical reads 19452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Association'. Scan count 1, logical reads 19452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SettlementStatus'. Scan count 1, logical reads 38905, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TransactionStatus'. Scan count 1, logical reads 38905, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'merchant'. Scan count 1, logical reads 1750680, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#B2596230'. Scan count 1, logical reads 19453, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#A586BBB9'. Scan count 1, logical reads 19454, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'transactionresponselog'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GatewayTransactionType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'POSEntryMode'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CurrencyCode'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
All the tables except transactionresponselog and transactionrequestlog are master tables.
