What I found is that there is a '%like% clause may be a good candidate.
I have eliminated the line and I get all the indexes.
Here I get a 30% table scan on tblpayment log and I get a hind to use and index. That I cannot, since I cannot add-remove indexes from those tables.
Then, there is another strange thing. Since I cannot get an execution plan with a temp table by just clicking on the execution plan, I am running the query with included execution plan.
I get the indexes. Then I turn the query back, using the "Like" clause and removing the temp table.
I get correct indexes!!
What I see is that I get the correct indexes, only if I run the query + include the execution plan (with '%like%'). If i just click to get an execution plan, I get the table scans.
I guess if I could see the temp table plan without running the query, maybe the execution plan will have a table scan in there(?)
Here is the complete query if anyone interested.
declare @Date AS DATETIME
select @date = '20161231'
declare @dateTo as dateTIME
--select @dateTo = '20170311'
select @dateTo = (@Date -60)
--AS
--BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--set statistics profile on
--temp table try
--select workstation_strname into #tmpwrks from tblWorkstation
--where workstation_strname LIKE '%UNCOL%'
SELECT CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112)),
Cinema_strName,
F.Film_strTitleAlt,
S.Session_dtmRealShow,
T.TransT_dtmRealTransTime,
S.Screen_bytNum,
T.ScreenD_strPhyRowId + T.ScreenD_strSeatId,
B.BookingH_strCardNo,
B.BookingH_dtmDateBooked,
P.PayLog_strBankTransNumber,
T.TransT_lgnNumber,
SUM(CASE
--WHEN
-- T.STax_strCode in ('S','T','U','V','W','X','Y')
--THEN
-- round(T.TransT_intNoOfSeats * ((T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)/(SELECT Ent_TAX + VAT FROM VISTA.dbo.ZZ_CinemaTax WHERE OperatorCode = T.CinOperator_strCode)+ (T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)),1)
WHEN
T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * (T.TransT_curRedempValueEach),4)
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''--Voucher
THEN
round(T.TransT_intNoOfSeats * (COALESCE(T.TransT_curRedempValueEach,0) + COALESCE(I.TransI_curValueEach,0)),4)
WHEN
T.STax_strCode in ('A')
THEN
round(T.TransT_intNoOfSeats * (T.TransT_curValueEach),4)
ELSE
round(T.TransT_intNoOfSeats * (T.TransT_curValueEach + COALESCE(I.TransI_curValueEach,0)),4)
END) AS Gross,
SUM(CASE
--WHEN
-- T.STax_strCode in ('S','T','U','V','W','X','Y')
--THEN
-- round(T.TransT_intNoOfSeats * ((T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)/(SELECT Ent_TAX + VAT FROM VISTA.dbo.ZZ_CinemaTax WHERE OperatorCode = T.CinOperator_strCode)+ (T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)),2) - T.TransT_intNoOfSeats * (T.TransT_curRedempTaxEach + T.TransT_curRedempTaxEach2)
WHEN
T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * (T.TransT_curRedempValueEach - T.TransT_curRedempTaxEach - T.TransT_curRedempTaxEach2),4)
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''--Voucher
THEN
round(T.TransT_intNoOfSeats * (T.TransT_curRedempValueEach - T.TransT_curRedempTaxEach - T.TransT_curRedempTaxEach2),4)
ELSE
round(T.TransT_intNoOfSeats * (T.TransT_curValueEach - T.TransT_curTaxAmount - T.TransT_curTaxAmount2),4)
END) AS Net,
SUM(CASE
WHEN
T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach2,4)
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> '' OR T.STax_strCode in ('S','T','U','V','W','X','Y')
THEN
round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach2,4)
ELSE
round(T.TransT_intNoOfSeats * T.TransT_curTaxAmount2,4)
END) AS FDTH,
SUM(CASE
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> '' OR T.STax_strCode in ('S','T','U','V','W','X','Y')
THEN
round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach,4)
WHEN
T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach,4)
ELSE
round(T.TransT_intNoOfSeats * T.TransT_curTaxAmount,4)
END) AS FPA,
SUM(CASE
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> ''
OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''
OR T.STax_strCode in ('S','T','U','V','W','X','Y')
OR T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curNetTotal,0)),4)
ELSE
round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curNetTotal,0)),4)
END) AS KRATHSEIS,
SUM(CASE
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> ''
OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''
OR T.STax_strCode in ('S','T','U','V','W','X','Y')
OR T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curSTaxEach,0)),4)
ELSE
round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curSTaxEach,0)),4)
END) AS FPAKRATHSEIS,
TransT_strStatus,
1 AS Scanned --COALESCE(EE.Entered,0) AS Scanned
,BD.Bookingd_strPickupWorkstn
FROM tblTrans_Ticket T
LEFT JOIN tblSession S
ON S.Session_lngSessionID = T.Session_lngSessionID
LEFT JOIN tblFilm F
ON F.Film_strCode = S.Film_strCode
LEFT JOIN tblBooking_Header B
ON B.TransC_lgnNumber = T.TransT_lgnNumber
LEFT JOIN tblPaymentLog P
ON P.TransC_lgnNumber = T.TransT_lgnNumber
LEFT JOIN dbo.tblBooking_Detail BD
ON BD.BookingD_intNextBookingNo = B.BookingH_intNextBookingNo
AND BD.BookingD_intSequence = T.TransT_intSequence
LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
SUM(TransI_curValueEach) AS TransI_curValueEach,
SUM(TransI_curNetTotal) AS TransI_curNetTotal,
SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
FROM tblTrans_Inventory
GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I
ON I.TransI_lgnNumber = T.TransT_lgnNumber
AND I.TransI_intPackageGroupNo = T.TransT_intPackageGroupNo
CROSS JOIN tblCinema
WHERE B.BookingH_dtmDateBooked >= @dateto
AND Session_dtmRealShow BETWEEN DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))) AND DATEADD(hh,24,DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))))
AND (P.PayLog_strAccepted = 'Y' OR B.BookingH_strSource = 'VIVA') --VIVA Transactions are not PayLog
AND TransT_strType = 'P'
AND B.BookingH_strSource IN ('CALL','VIVA','WWW','CELL') --ONLY ETICKETS
AND (EXISTS (SELECT * FROM dbo.tblEntryEvent EE WHERE EE.TransT_lgnNumber = T.TransT_lgnNumber AND EE.TransT_intSequence = T.TransT_intSequence AND EE.Workstation_strCode = BD.Bookingd_strPickupWorkstn) --Scanned at least once
-------- here I remove and add the temp table and the like clause
OR BD.Bookingd_strPickupWorkstn LIKE '%UNCOL%' or BD.Bookingd_strPickupWorkstn ='') --Or Collected by Uncollected Etickets Procedure ('=' for usher issues blank when error)
-- OR BD.Bookingd_strPickupWorkstn in (select workstation_strname from #tmpwrks) or BD.Bookingd_strPickupWorkstn ='')
GROUP BY Cinema_strName,
F.Film_strTitleAlt,
S.Session_dtmRealShow,
T.TransT_dtmRealTransTime,
S.Screen_bytNum,
T.ScreenD_strPhyRowId + T.ScreenD_strSeatId,
B.BookingH_strCardNo,
B.BookingH_dtmDateBooked,
P.PayLog_strBankTransNumber,
T.TransT_lgnNumber,
TransT_strStatus,
BD.Bookingd_strPickupWorkstn
option(recompile)
--drop table #tmpwrks
-- set statistics profile off
--select * from tblWorkstation where workstation_strname LIKE '%UNCOL%'