Stange issue converting datetime to varchar breaks table indexes!

Hi.
So I've freed the cache
I get the "wrong" execution plan.
A note here:
It only gave me one FREEPROCCACHE 0x00etcetcetc..... on live server (that I tried to free the cache) but on test server I've got 3 plans, That's strange since live server are heavily used an they should have more cache.

Anyhow I get the wrong plain again.

Thanks

Hello again.
I've create a function that returns a datetime (WITH SCHEMABINDING) . Now i get a loop instead of a heap but I can't get a correct plan with an index as I get a spool and a table scan

Hi.
I think I was able to get the query plan correct by "removing" the index with this approach:

	WHERE	B.BookingH_dtmDateBooked >= '2016-10-13 00:00:00.000' --  @dateTo -- (select top 1 x from #tempX ) 
			AND Session_dtmRealShow BETWEEN DATEADD(hh,6,@Date) AND DATEADD(hh,24,DATEADD(hh,6,@Date))	
		AND TransT_strType = 'P'           
			and I.TransI_lgnNumber + 0 = I.TransI_lgnNumber

but I am not sure if it is a correct one.

Any thoughts?

P.S. I think better like this:
AND (TransT_strType = 'P' or I.TransI_lgnNumber + 0 = I.TransI_lgnNumber)
as there may be some missing TransI_lgnNumber

No it gives wrong number of column.
Damn!

You don't have the right index/es / constraints defined on your table
tblTrans_Inventory - heap table and no indexes on join columns

"removing" the index with this approach:

You force the engine to generate a new execution plan, for which it made your plan.
This query needs "plan stability" = no matter of the values you supplied , should have the same execution plan

1 Like

This has been mention again on some of my posts.
This is a third party database so I'm not allowed to change or add indexes.
I'm trying to do the best I can without the possibility to "play" with the indexes

Thanks.

In this, to force a particular plan, case you can use :

-Query store if you are in SQLServer2016
-plan guid - see sp_create_plan_guide
-join hint LOOP | HASH | MERGE
-option hint

Or maybe let it in responsibility of the optimizer to choose the plan. In 99% cases, it knows best

1 Like

Thanks.
I do not think there is much I can do.
It would be a very difficult task to "mess" with the SQL Plan.

Should try to make the code more sargable but I guess that's about it for this query.

Thanks.

So.
Strange thing but today the execution plan seems to work.
So now it understands and gets the correct execution plan.
I've checked this in 8 primary SQL servers and the execution plan is build correctly on every single one of them.
So It may be that we are update statistics and the software does a rebuild indexes every week.
That's what every server has in common, so without being 100% sure, it may as well be (was) the problem.

No.
Disregard, I was getting another set of data.
Problem stays.

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%'