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