Оптимизируйте сложный запрос

Выполнение приведенной ниже процедуры занимает 9 секунд, а в основной таблице всего 30000 записей.

   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.**

Стол «Рабочий стол». Количество сканирований 3, логических чтений 55959, физических чтений 0, упреждающих чтений 1158, логических чтений больших объектов 0, физических чтений больших объектов 0, упреждающих чтений больших объектов 0. Таблица «Рабочий файл». Количество сканирований 0, логических операций чтения 0, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Таблица 'Журнал Запроса Транзакции'. Счетчик сканирований 1, логических операций чтения 2979, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Таблица «Иерархия продуктов». Количество сканирований 1, логических операций чтения 1, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Таблица 'POSConditionCode'. Счетчик сканирований 1, логических операций чтения 19452, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов. Таблица «# B1653DF7». Счетчик сканирований 1, логических операций чтения 19452, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Таблица «Ассоциация». Счетчик сканирований 1, логических операций чтения 19452, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Таблица «Статус расчетов». Количество сканирований 1, логических чтений 38905, физических чтений 0, упреждающих чтений 0, логических чтений больших объектов 0, физических чтений больших объектов 0, упреждающих чтений больших объектов 0.

Таблица «Статус транзакции». Количество сканирований 1, логических чтений 38905, физических чтений 0, упреждающих чтений 0, логических чтений больших объектов 0, физических чтений больших объектов 0, упреждающих чтений больших объектов 0.

Таблица 'торговец'. Количество сканирований 1, логических чтений 1750680, физических чтений 0, упреждающих чтений 0, логических чтений больших объектов 0, физических чтений больших объектов 0, упреждающих чтений больших объектов 0. Таблица '#B2596230'. Количество сканирований 1, логических чтений 19453, физических чтений 0, упреждающих чтений 0, логических чтений больших объектов 0, физических чтений больших объектов 0, упреждающих чтений больших объектов 0. Таблица «#A586BBB9». Счетчик сканирований 1, логических операций чтения 19454, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов. Стол «Рабочий стол». Количество сканирований 0, логических операций чтения 0, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Таблица 'журнал ответов по транзакциям'. Счетчик сканирований 1, логических операций чтения 2110, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Таблица 'Тип Транзакции Шлюза'. Количество сканирований 1, логических операций чтения 1, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Таблица 'POSEntryMode'. Количество сканирований 1, логических операций чтения 1, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Таблица 'Код валюты'. Количество сканирований 1, логических операций чтения 5, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Все таблицы, кроме transactionresponselog и transactionrequestlog, являются главными таблицами.