SQLTeam.com | Weblogs | Forums

Please help on conditional join


#1

Hi.
As per this post "DB HUGE! Help on indexes and ideas"
(thanks Kristen for all the help).

As I currently did not get an OK for a new intermediate database (hopefully in the future),
I'm trying to do the same thing the query does in a temp table.
This will run in 15 seconds vs 15 minutes when using the commented out selection described above.
So as you can see, there is a conditional selection right after tblClientOrderItem that I have commented out.
I'm just inserting all the data and then I try to recreate the conditional join. I try this by using a counter , inner join the same temp table and then apply the conditional join. If I use a where clause in the commented out code, in the temp db, I just get 70 rows vs 30000+ rows that I get in the normal query.
So I tried it that way.
However when using the temp query I get 2-3000 additional rows in the end result.
So as I'm no expert i think there is something I am missing so i can "simulate" the initial left join with the commented out condition.
I would appreciate any help. I'm thinking that adding the extra columns so i can recreate the join is initially adding rows to the query on the left join.I'm kinda lost here so...



declare	@Date as DateTime
declare	@Mode as smallint = 0

--set @Date = '20161207' -- test vm db  test
set @date = '20170418'


SELECT DISTINCT 	[Cinema],[Accounting_Type],[Box_Type],[SCR_SUBCIN],[POS],[kin_payment],[TransT_lgnNumber],[TransT_intSequence]
	,[CinOperator_strCode],[PGroup_strCode],[PGroup_strName],[PayType_strType],[PayType_strDescription],[Refunded]
	,[IsRefund],[PaymentFlag],[IsFree],[IsJournalist],[IsGiftCard],[IsMovieStore],[IsGold],[IsEuropa]
	--,Case when PV.VoucherCode is not null then null else CONVERT(VARCHAR,VC.sCode) end
	,[Booking]
	,VC.sName,[SeqRefunded],[STax_curRate],[Gross3],[Gross2],[Gross],[Net],[FDTH],[FPA],[KRATHSEIS],[FPAKRATHSEIS]
	,[Booking_WorkstationCode],[Booking_WorkstationName],[Collected_WorkstationCode],[Collected_WorkstationName]
	,[TType_strCode],[TicketType],[WGroup_strCode],[SalesChannel],[BUSINESS_DATE],[BOOKING_DATE],[BOOKING_ActualDATE]
	,[COLLECTED_DATE],[COLLECTED_ActualDATE],[IsGoldEuropa],[IsJuice],[OtherType],[Film_strCode],[Screen_bytNum]
	,[Item_HOPK],[TransI_curDiscount],[DiscountR_strPriceOffVariable],[DiscountR_curDiscPerCent],[MasterHOPK]
	,[FDTHCode],[FPACode],[lClientOrderID],[IsComplimentary],[Quantity],[TransI_intFeatureSeq],[InitialSalesChannel],[Session_dtmRealShow] -- this is as far as the original query goes, the below lines are added for --tempdb
	,TransT_strBarcodeRedemp,TransT_strVoucherBarcode,VStock_strBookletIdent,COI.lStartVoucherNumber,COI.lQtySupplied,
	ROW_NUMBER() OVER (ORDER BY [Cinema]) AS rownum
	into #tempB 
FROM ZZ_EAI_SourceData_VM
LEFT JOIN VISTAVM.DBO.tblStock VS

		ON	CASE WHEN TransT_strBarcodeRedemp <> '' THEN TransT_strBarcodeRedemp ELSE TransT_strVoucherBarcode END = 
		    CASE LEN(CASE WHEN TransT_strBarcodeRedemp <> '' THEN TransT_strBarcodeRedemp ELSE TransT_strVoucherBarcode END) 
			WHEN 20 THEN LEFT(VStock_strBookletIdent,4) + '000000000' + RIGHT(VStock_strBookletIdent,7)
			ELSE vs.Stock_strBarcode END

LEFT JOIN VISTAVM.DBO.tblClientOrderItem COI ON	VS.lvouchertypeid = COI.lvouchertypeid

		                             
	 -------This is the cut down selection, will bring less columns if used 
	    
	--AND (((LEN(TransT_strBarcodeRedemp)=20 and RIGHT(TransT_strBarcodeRedemp,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1)
	--  OR (LEN(TransT_strBarcodeRedemp)<>20 and RIGHT(VStock_strBookletIdent,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1))

	--OR
	--((LEN(TransT_strVoucherBarcode)=20 and RIGHT(TransT_strVoucherBarcode,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1)
	--  OR (LEN(TransT_strVoucherBarcode)<>20 and RIGHT(VStock_strBookletIdent,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1))

	--	)




	--	AND COI.lQtySupplied>0


LEFT JOIN VISTAVM.DBO.tblClientOrder CO	ON CO.lid = COI.lclientorderid
LEFT JOIN VISTAVM.DBO.tblClient VC ON VC.lid = CO.lclientid
--LEFT JOIN SAPBRIDGE.dbo.PaidVoucher PV ON left(TransT_strBarcodeRedemp,4) = PV.VoucherCode collate Greek_CI_AI
	--	                               OR left(TransT_strVoucherBarcode,4) = PV.VoucherCode collate Greek_CI_AI
WHERE COLLECTED_DATE = @Date
and ((isnull(TransT_strBarcodeRedemp,'')='' and isnull(TransT_strVoucherBarcode,'') = '') 

or
   CASE LEN(TransT_strBarcodeRedemp) 
			WHEN 20 THEN left(TransT_strBarcodeRedemp,4) + '000000000' + right(TransT_strBarcodeRedemp,7) --20 digits
			ELSE left(TransT_strBarcodeRedemp,4) + '000000' + right(TransT_strBarcodeRedemp,7) END  -- 17 digits
			=TransT_strBarcodeRedemp 

or

CASE LEN(TransT_strVoucherBarcode) 
			WHEN 20 THEN left(TransT_strVoucherBarcode,4) + '000000000' + right(TransT_strVoucherBarcode,7) --20 digits
			ELSE left(TransT_strVoucherBarcode,4) + '000000' + right(TransT_strVoucherBarcode,7) END  -- 17 digits
			=TransT_strVoucherBarcode


)


select * from #tempB T right join #tempB as TB on T.rownum = TB.rownum
--try to recreate teh join, getting extra rows
and
 (((LEN(T.TransT_strBarcodeRedemp)=20 and RIGHT(T.TransT_strBarcodeRedemp,7) BETWEEN T.lStartVoucherNumber AND T.lStartVoucherNumber + T.lQtySupplied - 1)
	  OR (LEN(T.TransT_strBarcodeRedemp)<>20 and RIGHT(T.VStock_strBookletIdent,7) BETWEEN T.lStartVoucherNumber AND T.lStartVoucherNumber + T.lQtySupplied - 1))

	OR
	((LEN(T.TransT_strVoucherBarcode)=20 and RIGHT(T.TransT_strVoucherBarcode,7) BETWEEN T.lStartVoucherNumber AND T.lStartVoucherNumber + T.lQtySupplied - 1)
	  OR (LEN(T.TransT_strVoucherBarcode)<>20 and RIGHT(T.VStock_strBookletIdent,7) BETWEEN T.lStartVoucherNumber AND T.lStartVoucherNumber + T.lQtySupplied - 1))

		)

AND T.lQtySupplied>0

drop table #tempB

DB HUGE! Help on indexes and ideas
#2

OK.
As I can think of, the tblClientOrderItem is checked against the other query on every column that the conditional join implies. So by cutting it down in the first place , I'm removing data that would have been checked against so I add rows that cannot be checked in the temp db.
Is that about right?
If so, can I do something about that?
I need to lighten up the rows of the tblClientOrderItem as they are millions and is slowing the query about 1000%