Stange issue converting datetime to varchar breaks table indexes!

sapator · March 16, 2017, 10:20am

Hello.
I'm fixing a query that results in a table scan and I'm trying to trigger the index seek that it has.
I'm having a very strange problem.



---- etc 
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>=  '2016-12-12'
			AND Session_dtmRealShow BETWEEN .......... etc

B.BookingH_dtmDateBooked is DATETIME

Result:

Now when I do this:


declare @dateTo as date
set @dateTo = '20161212'
set @dateTo =  CONVERT(DATE,CONVERT(NVARCHAR,@DateTo,112))

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

The plain changes entirely and I get a table scan and a HASH match (!!)

How is that possible?! I have tried various conversions on the @DateTo . Is there a possibility that @dateTo is treated differently than the '2016-12-12' ?

What can i try?
I can post the entire code if required btw the hash match comes from this:tblTrans_Inventory

stepson · March 16, 2017, 12:05pm

In your first query , you are using , as value, a constant '2016-12-12' .This is known to engine from beginning ,so it can built an optimal execution plan.
for the second query, you are using variable, and the value inside your variable is unknown for optimizer/engine . Variable value can not be sniffed. Base on this , the engine will optimize for unknown where a hash join is more suitable ( I believe , the estimate is 30 percent of the input’s cardinality)
See here more info : http://sqlmag.com/sql-server/sql-server-query-optimization-no-unknown-unknowns
On your second query, you can add OPTION (RECOMPILE) so that - the value of your variable will be now known.

sapator · March 16, 2017, 12:34pm

Hi.
I have oversimplified the query so I can post it here, left only the basics.
Option(recompile) does not seem to work.

I am using it on 2 place, on both or separate but it does not work.

Can you tell me if I am using it wrong?
Thanks.


declare	@Date AS DATETIME
set @date = '20170113'

declare @dateTo as datetime 
select @dateTo = (@Date -30) OPTION	(RECOMPILE)  --place 1



	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	
	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.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)
			
				ELSE
					round(T.TransT_intNoOfSeats * (T.TransT_curValueEach +  ISNULL(I.TransI_curValueEach,0)),4)
			END) AS Gross,
			
			TransT_strStatus,
            1 AS Scanned  
	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,@Date) AND DATEADD(hh,24,DATEADD(hh,6,@Date))	
		AND TransT_strType = 'P'     
         
			
	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
         	OPTION	(RECOMPILE)  --Place2

stepson · March 16, 2017, 12:49pm

The only place where should be , is in place 2.

If I understood clearly, the same , but you replace B.BookingH_dtmDateBooked >= @dateTo with B.BookingH_dtmDateBooked >= '20170113' ? Nothing else changed ?

sapator · March 16, 2017, 1:06pm

Nothing else changed and the execution plan stays the same when i put it on place 2
thanks.

sapator · March 16, 2017, 1:24pm

The problem also seems to be eliminated in here:

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_curRedempValueEach),4)
		
			ELSE
				round(T.TransT_intNoOfSeats * (T.TransT_curValueEach +  ISNULL(I.TransI_curValueEach,0)),4)
		END) AS Gross,

I'm not sure if it is the round or the sum but if i remove the round (for example do then 1 else 1 ) I get the index.
It does not work if i try to remove the isnull like so:

round(T.TransT_intNoOfSeats * (T.TransT_curValueEach + (case I.TransI_curValueEach when NULL then 0 else  I.TransI_curValueEach end)),4)

stepson · March 16, 2017, 1:25pm

In your graphical execution plan is a table tblEntryEvent. This is not shown in the text of your query. It this right ?

sapator · March 16, 2017, 1:27pm

Oh.
Yes I edited and removed it since I wanted to make the query smaller and it did not impact the execution plan but I have posted the execution plan before the removal.

sapator · March 16, 2017, 1:28pm

Also can this be a collation issue?
I don't think so but I'm out of ideas now.

stepson · March 16, 2017, 1:30pm

To be clear, maybe post the new execution plan1 and new execution plan2 (the one with hash) for the new query.

sapator · March 16, 2017, 1:46pm

OK.
Let me post the data analytically in a minute...

sapator · March 16, 2017, 1:57pm

Plan 1 - problematic

declare	@Date AS DATETIME
set @date = '20170113'

declare @dateTo as date
set @dateTo = '20170101'
--select @dateTo = (@Date -30) OPTION	(RECOMPILE)



	

	
	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.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)
			
				ELSE
				-- 	round(T.TransT_intNoOfSeats * (T.TransT_curValueEach + (case I.TransI_curValueEach when NULL then 0 else  I.TransI_curValueEach end)),4) -- ISNULL(I.TransI_curValueEach,0)),4)
						round(T.TransT_intNoOfSeats * (T.TransT_curValueEach + (case I.TransI_curValueEach when NULL then 0 else  I.TransI_curValueEach end)),4) -- ISNULL(I.TransI_curValueEach,0)),4)
			END) AS Gross,
			
			TransT_strStatus,
            1 AS Scanned  
	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,@Date) AND DATEADD(hh,24,DATEADD(hh,6,@Date))	
		AND TransT_strType = 'P'           
			
	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
         	OPTION	(RECOMPILE);

Plan 2 -OK
(change: WHERE B.BookingH_dtmDateBooked >= '20170101' )

Note! I get a missing index here that I do not get with the @DateTo ( the plot thickens)

stepson · March 16, 2017, 2:19pm

Hash join is used when there is no good index to use on that join.
You should check the indexes existing on those 2 tables tblTrans_XXX
and if needed , add it to test

Are you on test environment? If yes, did you tried to clear the plan cache? (if only on test environment)
to try on a fresh cache

sapator · March 16, 2017, 2:26pm

OK maybe there is no good index but how on earth the same thing is giving missing index on one plan and non missing on the other plan?!!!

Also can you help on clearing the plan cache?
What exactly should I clear? All the server cache?
I would like to avoid that, even if I am on test environment. Can I do a clear cache for this specific query? If so how?
Thanks.

mmkrishna1919 · March 16, 2017, 2:30pm

You have index on column BookingH_dtmDateBooked. So statistics object will be created automatically.

In case of BookingH_dtmDateBooked>= '2016-12-12', the optimiser knows the value so it will find estimated number of rows exactly from statistics object Histogram.

Where as in case of BookingH_dtmDateBooked>= @dateTo the optimiser doesn't know the value of variable(@date) while preparing the execution plan. So optimiser does standard guess of 30% selectivity for inequality comparisons as estimated number of rows.

In first case the estimated number of rows are may be less so optimiser choosen Nested Loop Join operatot for join. Where as in second case 30% of total number of rows it may be quite high so optimiser choosen HASH match for join.

stepson · March 16, 2017, 2:31pm

SELECT plan_handle, st.text
FROM
sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%something representativ%';

DBCC FREEPROCCACHE (0x0...........)

see here more details:
https://msdn.microsoft.com/en-us/library/ms174283.aspx

sapator · March 16, 2017, 2:37pm

@mmkrishna1919 so isn't that fixed with Option (Recompile) ? That does not work.
In general can i fix that?

@stepson what do i put to WHERE text LIKE N'%something representativ%'; ? All the query?
Thanks.

stepson · March 16, 2017, 3:02pm

@sapator : something like this "%SELECT CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112)),
Cinema_strName,
F.Film_strTitleAlt,
S.Session_dtmRealShow%"

so that you find the exact plan handle

sapator · March 16, 2017, 3:22pm

So is this viable according to what mmkrishna1919 said?
Also on test (since the data is smaller) I get an index scan and the "correct" plan.
I'm confused on what to do and my brain hurts.

stepson · March 16, 2017, 6:01pm

So is this viable according to what mmkrishna1919 said?

Yes - take a look of my first reply. Base ideea is the same as mmkrishna
Only one thing if in my mind now: when you are using constant/literal '20170101' there is already an execution plan in cache, and it is using it