Hi.
I'm starting a clean up on a 2500 rows query and I'm taking it step by step.
I have the following part of code that I see a big table scan.
I will first try to fix the conversions and see if I can get the same amount of rows but then I can also see that I have no index for the "TransT_dtmRealTransTime" , so I would appreciate any advice.
I'm attaching info:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
declare @PICK_UP_BOX varchar(30)
declare @USER varchar(30)
declare @Z_Date datetime
set @Z_Date = '20170202'
DECLARE @USERNO as INT
SET @USERNO = CASE
WHEN
@USER = 'UserNo'
THEN
-1
ELSE
CONVERT(smallint,@USER)
END
DECLARE @CINEMA as varchar(2)
SET @CINEMA = (
SELECT Cinema_strCode
FROM VISTA.DBO.tblCinema
)
------------------------------------Cashier1-------------------------------------
SELECT *
FROM (
SELECT DISTINCT
@CINEMA as Cinema,
2 AS Accounting_Type,
'3' AS kin_payment,
T.TransT_lgnNumber,
T.TransT_intSequence,
'' AS CinOperator_strCode,--T.CinOperator_strCode,
'' AS PGroup_strCode,--T.PGroup_strCode,
'' AS PGroup_strName,--Pg.PGroup_strName,
'' as PayType_strType,--PT.PayType_strType,
CASE
WHEN
ISNULL(T.TransT_strBarcodeRedemp,'') <> ''--Voucher
THEN
'Voucher'
ELSE
'Free'
END AS PayType_strDescription,
CASE
WHEN
TransT_strStatus = 'R'
THEN
1
ELSE
0
END AS Refunded,
CASE
WHEN
ISNULL(T.TransT_strBarcodeRedemp,'') <> ''
THEN
'V'
ELSE
'F'
END AS PaymentFlag,
'' AS sCode,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
1
ELSE
0
END AS BOOKING,
'' AS sName,
0 as SeqRefunded,
0 AS Gross,
CASE
WHEN
ISNULL(T.TransT_strBarcodeRedemp,'') <> '' OR ISNULL(T.TransT_strVoucherBarcode,'') <> ''--Voucher
THEN
TransT_intNoOfSeats * (ISNULL(TransT_curRedempValueEach,0) + ISNULL(TransT_curConcGrossValue,0))
ELSE
round(TransT_intNoOfSeats * ((SELECT price_curprice FROM tblprice WHERE Pgroup_strCode = T.Pgroup_strCode AND Price_strCode = T.Price_strCode)/(SELECT Ent_TAX + VAT FROM VISTA.DBO.ZZ_CinemaTax WHERE OperatorCode = T.CinOperator_strCode)+ (SELECT price_curprice FROM tblprice WHERE Pgroup_strCode = T.Pgroup_strCode AND Price_strCode = T.Price_strCode)),2)
END AS Gross2,
0 AS Gross3,
0 AS Net,--CASE
0 AS FDTH,--CASE
0 AS FPA,--CASE
0 AS KRATHSEIS,--CASE
0 AS FPAKRATHSEIS,--CASE
T.Workstation_strCode as Booking_WorkstationCode,
W.Workstation_strName as Booking_WorkstationName ,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
BH.BookingH_strPickupWorkstn
ELSE
T.Workstation_strCode
END AS Collected_WorkstationCode,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
BW.Workstation_strName
ELSE
W.Workstation_strName
END AS Collected_WorkstationName,
TT.TType_strCode,
TType_strDescription AS TicketType,
WG.WGroup_strCode,
WG.WGroup_strDescription AS SalesChannel,
PS.POSS_dtmBusinessDate AS BUSINESS_DATE,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
CASE
WHEN
BH.BookingH_dtmDateBooked BETWEEN convert(datetime,convert(char(10),BH.BookingH_dtmDateBooked,101)) AND DATEADD(HOUR,6,convert(datetime,convert(char(10),BH.BookingH_dtmDateBooked,101)))
THEN
convert(datetime,convert(char(10),BH.BookingH_dtmDateBooked -1,101))
ELSE
convert(datetime,convert(char(10),BH.BookingH_dtmDateBooked,101))
END
ELSE
CASE
WHEN
TransT_dtmRealTransTime BETWEEN convert(datetime,convert(char(10),TransT_dtmRealTransTime,101)) AND DATEADD(HOUR,6,convert(datetime,convert(char(10),TransT_dtmRealTransTime,101)))
THEN
convert(datetime,convert(char(10),TransT_dtmRealTransTime -1,101))
ELSE
convert(datetime,convert(char(10),TransT_dtmRealTransTime,101))
END
END AS BOOKING_DATE,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
BH.BookingH_dtmDateBooked
ELSE
T.TransT_dtmRealTransTime
END AS BOOKING_ActualDATE,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
CASE
WHEN
BH.BookingH_dtmDateCollected BETWEEN convert(datetime,convert(char(10),BH.BookingH_dtmDateCollected,101)) AND DATEADD(HOUR,6,convert(datetime,convert(char(10),BH.BookingH_dtmDateCollected,101)))
THEN
DATEADD(DAY,-1,convert(datetime,convert(char(10),BH.BookingH_dtmDateCollected ,101)))
ELSE
convert(datetime,convert(char(10),BH.BookingH_dtmDateCollected,101))
END
ELSE
CASE
WHEN
TransT_dtmRealTransTime BETWEEN convert(datetime,convert(char(10),TransT_dtmRealTransTime,101)) AND DATEADD(HOUR,6,convert(datetime,convert(char(10),TransT_dtmRealTransTime,101)))
THEN
DATEADD(DAY,-1,convert(datetime,convert(char(10),TransT_dtmRealTransTime ,101)))
ELSE
convert(datetime,convert(char(10),TransT_dtmRealTransTime,101))
END
END AS COLLECTED_DATE,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
BH.BookingH_dtmDateCollected
ELSE
T.TransT_dtmRealTransTime
END AS COLLECTED_ActualDATE,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
BH.BookingH_intPickupUser
ELSE
T.User_intUserNo
END AS PickUpUserNo,
User_strLastName + ' ' + User_strFirstName AS PickUpUserName,
'' AS ItemDescription
FROM VISTA.DBO.tblTrans_ticket T
LEFT JOIN VISTA.DBO.tblWorkstation W
ON T.Workstation_strCode = W.Workstation_strCode
LEFT JOIN VISTA.DBO.tblPos_Sess PS
ON T.TransT_lngPOSSessionID = PS.POSS_intPOSSessionNumber
LEFT JOIN VISTA.DBO.tblTicketType TT
ON TT.TType_strCode = T.Price_strCode
LEFT JOIN VISTA.DBO.tblBooking_Header BH
ON T.TransT_lgnNumber = BH.TransC_lgnNumber
LEFT JOIN VISTA.DBO.tblWorkstation BW
ON BW.Workstation_strCode = BH.BookingH_strPickupWorkstn
LEFT JOIN VISTA.DBO.tblWorkstation_Group WG
ON WG.WGroup_strCode = W.WGroup_strCode
LEFT JOIN VISTA.DBO.tblPrice_Group PG
ON T.PGroup_strCode = PG.PGroup_strCode
-- LEFT JOIN VISTAVM.DBO.tblStock VS
-- ON left(convert(varchar,CASE WHEN ISNULL(T.TransT_strBarcodeRedemp,'') = '' THEN '00000000000' ELSE T.TransT_strBarcodeRedemp END),4) + right('000000000' + convert(varchar,right(CASE WHEN ISNULL(T.TransT_strBarcodeRedemp,'') = '' THEN '00000000000' ELSE T.TransT_strBarcodeRedemp END,len(CASE WHEN ISNULL(T.TransT_strBarcodeRedemp,'') = '' THEN '00000000000' ELSE T.TransT_strBarcodeRedemp END)-4)),7)
-- = left(convert(varchar,VS.VStock_strBookletIdent),4) + right('000000000' + convert(varchar,right(VS.VStock_strBookletIdent,len(VS.VStock_strBookletIdent)-4)),7)
-- LEFT JOIN VISTAVM.DBO.tblClient VC
-- ON VS.lIssuedLocationID = VC.sCode
LEFT JOIN VISTA.DBO.tblUser U
ON U.User_intUserNo = CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
BH.BookingH_intPickupUser
ELSE
T.User_intUserNo
END
WHERE ISNULL(T.TransT_strBarcodeRedemp,'') <> ''
OR T.STax_strCode in ('X','Y')
) T
WHERE CASE
WHEN
@Z_Date = '19000101'
THEN
@Z_Date
ELSE
ISNULL(COLLECTED_DATE,@Z_Date)
END = @Z_Date
AND CASE
WHEN
@PICK_UP_BOX = ''
THEN
@PICK_UP_BOX
ELSE
Collected_WorkstationCode
END = @PICK_UP_BOX
AND CASE
WHEN
@USERNO = -1
THEN
@USERNO
ELSE
PickUpUserNo
END = @USERNO
A first quick watch is that by removing this line:
WHERE ISNULL(T.TransT_strBarcodeRedemp,'') <> ''
OR T.STax_strCode in ('X','Y')I get a table scan but is reduced to 45% ( the execution time remains the same give or take).
)
