SQLTeam.com | Weblogs | Forums

Resolve table scan make sargable query


#1

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


#2

As A first attempt I got rid of the conversions (caution! I just got rid of them, i don't know if the additions or subtractions on the time checks work correctly)
But I get the 90% table scan again.

        GO
        SET QUOTED_IDENTIFIER OFF
        GO

    
        	declare @PICK_UP_BOX varchar(30)
        	declare @USER varchar(30)
        	declare @Z_Date datetime
        	set @Z_Date = '20170202'
        	set @user = 'UserNo'
        --AS
        --BEGIN

        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 BH.BookingH_dtmDateBooked AND DATEADD(HOUR,6,BH.BookingH_dtmDateBooked)
        							THEN
        							BH.BookingH_dtmDateBooked -1
        							ELSE
        								BH.BookingH_dtmDateBooked
        						END
        					ELSE
        						CASE
        							WHEN
        								TransT_dtmRealTransTime BETWEEN TransT_dtmRealTransTime AND DATEADD(HOUR,6,TransT_dtmRealTransTime)
        							THEN
        								TransT_dtmRealTransTime -1
        							ELSE
        								TransT_dtmRealTransTime
        						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 BH.BookingH_dtmDateCollected AND DATEADD(HOUR,6,BH.BookingH_dtmDateCollected)
        							THEN
        								DATEADD(DAY,-1,BH.BookingH_dtmDateCollected)
        							ELSE
        								BH.BookingH_dtmDateCollected
        						END
        					ELSE
        						CASE
        							WHEN
        								TransT_dtmRealTransTime BETWEEN TransT_dtmRealTransTime AND DATEADD(HOUR,6,TransT_dtmRealTransTime)
        							THEN
        								DATEADD(DAY,-1,TransT_dtmRealTransTime)
        							ELSE
        								TransT_dtmRealTransTime
        						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

        Slow as hell.

    Reply With Quote Reply With Quote Share on Google+

#3

Is always goign to be bad news - it prevents the query being SARGable. I would fix the data so that both NULL and BlankString are NOT allowed in the data. Either one or the other, not both. Then you can do:

T.TransT_strBarcodeRedemp, IS NULL

(or compare to BlankString if that is your choice, but personally I dislike BlankString for "unknown/missing/not-relevant" data, that's (usually) what NULL is for :slight_smile: )

I expect that this, similarly, is likely to be a choke-point:

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	

and that that would be better without the conditional-join. I've had situations like this before where two (mutually exclusive) JOINs performed better (similarly to avoid an OR in a WHERE clause two mutually-exclusive UNION ALL queries performed better).

I'm also concerned about the complex WHERE clause. It might be better to have a "near enough" WHERE clause, which favours the indexes etc. available and then an OUTER query that applied the exact, complex, filter criteria.

Alternatively:

WHERE		CASE
				WHEN
					@Z_Date = '19000101'
				THEN
					@Z_Date 
				ELSE
					ISNULL(COLLECTED_DATE,@Z_Date)
			END = @Z_Date

Isn't that better done as

WHERE		(@Z_Date = '19000101' OR COLLECTED_DATE = @Z_Date)
		AND (@PICK_UP_BOX = '' OR Collected_WorkstationCode = @PICK_UP_BOX)

?? but either way, a clutch of those sort of OR statements will be slower than a WHERE clause that only contains ANDs

NOTE that my clause does not handle the situation where COLLECTED_DATE IS NULL and @Z_Date <> '19000101 (which your query includes, and mine excludes - I've left out that "ability" just for brevity)

One way of solving this "complex" WHERE clause is to use Dynamic SQL. Build a WHERE clause [string in an @Variable] which only includes conditions that are required, and then EXEC that Dynamic SQL. Actually it would be better to use sp_ExcuteSQL and not EXEC because a) it is less prone to SQL Injection exploits and b) the Query Plan will be more readily cached.

DECLARE @WHERE nvarchar(MAX)
SELECT @WHERE = 'WHERE 1=1'
                + CASE WHEN @Z_Date = '19000101' THEN ''
                     ELSE ' AND COLLECTED_DATE = @Z_Date'
                     END
                + CASE WHEN @PICK_UP_BOX = '19000101' THEN ''
                     ELSE ' AND Collected_WorkstationCode = @PICK_UP_BOX'
                     END
... etc ...

and then use sp_ExecuteSQL with parameters defined for @Z_Date, @PICK_UP_BOX etc.


#4

Hi.
Will have to digest all that and test them.
Will be back with questions as I'm already confused in some points just by reading it :slight_smile:
Thanks in advance, will have a look...


#5

Hi.
From a quick first look about the ISNULL(T.TransT_strBarcodeRedemp,'') <> ''
This is not our database to develop so i can't really change to null's

I'm a little confused on what should I do to check blanks, as you suggested.
Should i do a Case?
Thanks.


#6

Hi.
Just a little info.
I have (for testing) removed all the barcode comparisons and replaced them with just IS NULL.
I have also removed the 'X','Y' as we currently have deprecated those specific taxes , and the query looks like this:

USE [VISTA]
GO

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'
	set @user = 'UserNo'


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
						T.TransT_strBarcodeRedemp is null--Voucher
					THEN
						'Voucher'
					ELSE
						'Free'
				END AS PayType_strDescription,
				CASE
					WHEN
						TransT_strStatus = 'R'
					THEN
						1
					ELSE
						0
				END	AS Refunded,
				CASE
					WHEN
						T.TransT_strBarcodeRedemp is null			
					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
						T.TransT_strBarcodeRedemp is null OR T.TransT_strVoucherBarcode is null--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 BH.BookingH_dtmDateBooked AND DATEADD(HOUR,6,BH.BookingH_dtmDateBooked)
							THEN
							BH.BookingH_dtmDateBooked -1
							ELSE
								BH.BookingH_dtmDateBooked
						END
					ELSE
						CASE
							WHEN
								TransT_dtmRealTransTime BETWEEN TransT_dtmRealTransTime AND DATEADD(HOUR,6,TransT_dtmRealTransTime)
							THEN
								TransT_dtmRealTransTime -1
							ELSE
								TransT_dtmRealTransTime
						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 BH.BookingH_dtmDateCollected AND DATEADD(HOUR,6,BH.BookingH_dtmDateCollected)
							THEN
								DATEADD(DAY,-1,BH.BookingH_dtmDateCollected)
							ELSE
								BH.BookingH_dtmDateCollected
						END
					ELSE
						CASE
							WHEN
								TransT_dtmRealTransTime BETWEEN TransT_dtmRealTransTime AND DATEADD(HOUR,6,TransT_dtmRealTransTime)
							THEN
								DATEADD(DAY,-1,TransT_dtmRealTransTime)
							ELSE
								TransT_dtmRealTransTime
						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	T.TransT_strBarcodeRedemp is null

		) T
WHERE		(@Z_Date = '19000101' OR COLLECTED_DATE = @Z_Date)
		AND (@PICK_UP_BOX = '' OR Collected_WorkstationCode = @PICK_UP_BOX)
		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

I get to create a non clustered index to "T.TransT_strBarcodeRedemp" . I cannot do that since I cannot modify the table as It is from a 3rd party developer.

So any thoughts? Or The query I posted has issues again?
Thanks.

I just have a thought of putting all the data to a temp table , without the barcode checks and then eliminate those values but I'm not sure...


#7

If you have to check for both NULL and BlankString your query will be slow. No way around that (except "fix the data"). You could fix the data by pulling it all into a local table and applying any Data Cleanup data-massaging that you need. But your local copy will be out-of-date when the remote changes, so this is only any good if, for example, you can refresh your local, "clean copy", overnight and then use that during the day.

You could also add a computed column for dealing with the LEFT JOIN VISTA.DBO.tblUser which is having to resolve on that Either/Or condition. (That may not be a bottleneck, currently, so worth checking IF it is ... but at the very least it won't scale well)

You might find that a UNION ALL runs faster, but it makes the code more unwieldy to maintain - you have to have everything repeated twice in the code, so more chance of errors during code maintenance)

Actually, thinking about it, you can't use a UNION ALL because the two tests are NOT mutually exclusive (or I can;t find a way to code them so that they are)

SELECT Col1, Col2, ...
FROM MyTable
WHERE T.STax_strCode in ('X','Y')
    		OR T.TransT_strBarcodeRedemp <> ''

might be enough - because if T.TransT_strBarcodeRedemp IS NULL that should be excluded from the results anyway.

That might help. The LEFT JOIN VISTA.DBO.tblUser is still probably a problem though.


#8

The check is not equal blanks. so you don't need to check for NULLs at all, just > blank:

T.TransT_strBarcodeRedemp > ''


#9

Is that "better" than using Not Equals?

I'm thinking that you are thinking of Indexes and Ranges and if so perhaps the Query optimiser does something smarter when using Greater Than rather than Not Equals?

(There COULD be some stuff "before" the space character, although its all a bit binary at that point. Leading TAB perhaps?)


#10

Hi.
I just need to ask if there is no way to eliminate the table scan if the table does not have an index.
Am asking because I did the following:
I have removed any equation and any "where" and imputed manual values. So in that sense (of course the data is messed up data but this is a test) I do not get any sargable issues that could potentially interfere with the tables.
So I ended up with the below:

USE [VISTA]
GO

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'
	set @user = 'UserNo'
--AS
--BEGIN

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
			----			T.TransT_strBarcodeRedemp <> ''--Voucher
		----			THEN
			----			'Voucher'
			----		ELSE
			----			'Free'         ------------voucher , free for later on 
			---	END AS PayType_strDescription,
			T.TransT_strBarcodeRedemp as PayType_strDescription,
				--CASE
				--	WHEN
				--		TransT_strStatus = 'R'
				--	THEN
				--		1
				--	ELSE
				--		0
				--END	AS Refunded,
				'R' as Refunded,
			--	CASE
			--		WHEN
					--	T.TransT_strBarcodeRedemp <> ''				
				--	THEN
					--	'V'
				--	ELSE
			--			'F'
		--		END AS PaymentFlag,   ----------for later
T.TransT_strBarcodeRedemp  AS PaymentFlag, 

				'' AS sCode,
				--CASE
				--	WHEN
				--		BH.BookingH_intNextBookingNo IS NOT NULL
				--	THEN
				--		1
				--	ELSE
				--		0
				--END	AS BOOKING,
				1 as BOOKING,
				'' AS sName,
				0 as SeqRefunded,
				0 AS Gross,

				----------
				-------
				---------
				----
				-----
		--		CASE 
			--		WHEN
			--			T.TransT_strBarcodeRedemp <> '' OR  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,
			1 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,
				'1' AS Collected_WorkstationCode,
				--CASE
				--	WHEN
				--		BH.BookingH_intNextBookingNo IS NOT NULL
				--	THEN
				--		BW.Workstation_strName
				--	ELSE
				--		W.Workstation_strName
				--END	AS Collected_WorkstationName,
				'1' 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 BH.BookingH_dtmDateBooked AND DATEADD(HOUR,6,BH.BookingH_dtmDateBooked)
				--			THEN
				--			BH.BookingH_dtmDateBooked -1
				--			ELSE
				--				BH.BookingH_dtmDateBooked
				--		END
				--	ELSE
				--		CASE
				--			WHEN
				--				TransT_dtmRealTransTime BETWEEN TransT_dtmRealTransTime AND DATEADD(HOUR,6,TransT_dtmRealTransTime)
				--			THEN
				--				TransT_dtmRealTransTime -1
				--			ELSE
				--				TransT_dtmRealTransTime
				--		END
				--END	AS BOOKING_DATE,
				'20170101' AS BOOKING_DATE,
				--CASE
				--	WHEN
				--		BH.BookingH_intNextBookingNo IS NOT NULL
				--	THEN
				--		BH.BookingH_dtmDateBooked
				--	ELSE
				--		T.TransT_dtmRealTransTime
				--END	AS BOOKING_ActualDATE,
				'20170101' AS BOOKING_ActualDATE,
				--CASE
				--	WHEN
				--		BH.BookingH_intNextBookingNo IS NOT NULL
				--	THEN
				--		CASE
				--			WHEN
				--				BH.BookingH_dtmDateCollected BETWEEN BH.BookingH_dtmDateCollected AND DATEADD(HOUR,6,BH.BookingH_dtmDateCollected)
				--			THEN
				--				DATEADD(DAY,-1,BH.BookingH_dtmDateCollected)
				--			ELSE
				--				BH.BookingH_dtmDateCollected
				--		END
				--	ELSE
				--		CASE
				--			WHEN
				--				TransT_dtmRealTransTime BETWEEN TransT_dtmRealTransTime AND DATEADD(HOUR,6,TransT_dtmRealTransTime)
				--			THEN
				--				DATEADD(DAY,-1,TransT_dtmRealTransTime)
				--			ELSE
				--				TransT_dtmRealTransTime
				--		END
				--END	AS COLLECTED_DATE,
				TransT_dtmRealTransTime as COLLECTED_DATE,
				--CASE
				--	WHEN
				--		BH.BookingH_intNextBookingNo IS NOT NULL
				--	THEN
				--		BH.BookingH_dtmDateCollected
				--	ELSE
				--		T.TransT_dtmRealTransTime
				--END	AS COLLECTED_ActualDATE,
				T.TransT_dtmRealTransTime AS COLLECTED_ActualDATE,
				--CASE
				--	WHEN
				--		BH.BookingH_intNextBookingNo IS NOT NULL
				--	THEN
				--		BH.BookingH_intPickupUser
				--	ELSE
				--		T.User_intUserNo
				--END	AS PickUpUserNo,
				T.User_intUserNo AS PickUpUserNo,
			--	User_strLastName + ' ' + User_strFirstName AS PickUpUserName,
			'1' 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	T.TransT_strBarcodeRedemp <> '' 
	-- depricated --	OR T.STax_strCode in ('X','Y')
	--	) T
--WHERE				(@Z_Date = '19000101' OR COLLECTED_DATE = @Z_Date)
		--AND (@PICK_UP_BOX = '' OR Collected_WorkstationCode = @PICK_UP_BOX)
		--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

		WHERE			 T.TransT_dtmDateTime = @Z_Date
	--	where            T.TransT_dtmRealTransTime = @Z_Date

Taking a look at the last line of code you will see a where.
TransT_dtmDateTime is indexed but TransT_dtmRealTransTime is not. So running one or the other will result in an indexed or table scan. The problem here is that the table used the TransT_dtmRealTransTime for the selects that is an entirely different data.
see images:

What I am thinking but I have idea if it is possible is if I can somehow "trick" the query so I can make the datetime as realtranstime.
Anyhow this is something else, as If I do the following I still get a table scan.And the following is using the Not Equals to the barcode (so I can fix one sargable issue) and in the case that uses the realTranstime, I replace it with the dateTime.
But this will still give me a table scan, even though , as I've said, dtmdateTime is indexed.

I'm really stick here :frowning:

here is the refined code

USE [VISTA]
GO
/******  Script Date: 3/2/2017 4:32:41 μμ ******/
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'
	set @user = 'UserNo'
--AS
--BEGIN

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
					T.TransT_strBarcodeRedemp <> ''
					THEN
						'Voucher'
					ELSE
						'Free'
				END AS PayType_strDescription,
				CASE
					WHEN
						TransT_strStatus = 'R'
					THEN
						1
					ELSE
						0
				END	AS Refunded,
				CASE
					WHEN
					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
					T.TransT_strBarcodeRedemp <> '' OR 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 BH.BookingH_dtmDateBooked AND DATEADD(HOUR,6,BH.BookingH_dtmDateBooked)
							THEN
							BH.BookingH_dtmDateBooked -1
							ELSE
								BH.BookingH_dtmDateBooked
						END
					ELSE
						CASE
							WHEN
								TransT_dtmDateTime BETWEEN TransT_dtmDateTime AND DATEADD(HOUR,6,TransT_dtmDateTime)
							THEN
								TransT_dtmDateTime -1
							ELSE
								TransT_dtmDateTime
						END
				END	AS BOOKING_DATE,
				CASE
					WHEN
						BH.BookingH_intNextBookingNo IS NOT NULL
					THEN
						BH.BookingH_dtmDateBooked
					ELSE
						T.TransT_dtmDateTime
				END	AS BOOKING_ActualDATE,
				CASE
					WHEN
						BH.BookingH_intNextBookingNo IS NOT NULL
					THEN
						CASE
							WHEN
								BH.BookingH_dtmDateCollected BETWEEN BH.BookingH_dtmDateCollected AND DATEADD(HOUR,6,BH.BookingH_dtmDateCollected)
							THEN
								DATEADD(DAY,-1,BH.BookingH_dtmDateCollected)
							ELSE
								BH.BookingH_dtmDateCollected
						END
					ELSE
						CASE
							WHEN
								TransT_dtmDateTime BETWEEN TransT_dtmDateTime AND DATEADD(HOUR,6,TransT_dtmDateTime)
							THEN
								DATEADD(DAY,-1,TransT_dtmDateTime)
							ELSE
								TransT_dtmDateTime
						END
				END	AS COLLECTED_DATE,
				CASE
					WHEN
						BH.BookingH_intNextBookingNo IS NOT NULL
					THEN
						BH.BookingH_dtmDateCollected
					ELSE
						T.TransT_dtmDateTime
				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	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

#11

Hello again.
What I can do is this:
FROM VISTA.DBO.tblTrans_ticket T with (index = indDateTime)
LEFT JOIN VISTA.DBO.tblWorkstation W ...etc

I get an RID LOOKUP. Possible because I have no cluster index on the table

Is that an improvement? As I'm forcing the compiler to use the index
thanks.


#12

Nop.
Takes more time.