SQLTeam.com | Weblogs | Forums

Stange issue converting datetime to varchar breaks table indexes!


#21

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


#22

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


#23

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!


#24

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


#25

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.


#26

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


#27

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.


#28

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.


#29

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


#30

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