DB HUGE! Help on indexes and ideas

sapator · April 19, 2017, 7:47am

Hi.
I'm having a 1.108,500 mb space (am assuming this is 1GB db unless I'm blind).
The problem is that I get unbelievable huge data reads on the execution plan and execution times that are over 20 minutes.
I have the query and I'm cutting it down (there are some nasty select cases that I will cut down on the second code here ut the problem remains).
the good part here is that this is an internal DB and I can include indexes at a logical level.
So any assistant can be valuable.
OK.
Here is the portion of the code that I have cut down. I've tried to remove the cases and conversions so I can have a clean start and be sure that the query will run faster. Even with the cut down cases I still get data that is unbelievable high.
Please note that this is inverted to a database but I have removed the delete from and insert into part and left only the select portion:

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

	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]
FROM ZZ_EAI_SourceData_VM
LEFT JOIN VISTAVM.DBO.tblStock VS on TransT_strBarcodeRedemp = TransT_strVoucherBarcode 

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

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

WHERE COLLECTED_DATE = @Date

And here is the complete code. You can see that I'm also joining with an external DB (on the same server) but the problem will start on the cut of version, so this is not the main issue here.

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

	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]
FROM ZZ_EAI_SourceData_VM
LEFT JOIN VISTAVM.DBO.tblStock VS -- on TransT_strBarcodeRedemp = TransT_strVoucherBarcode 

		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

		                                
	    
	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

--WHERE COLLECTED_DATE = @Date




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


)

I'm also including the sentry explorer data consumption for the cut down code, if that could help.

sapator · April 19, 2017, 9:11am

Hmm.
Unfortunately I just noticed that we are using a 3rd party db (namely VISTAVM) that the ZZ_EAI_SourceData_VM is inside.
Now, I can change any index on ZZ_EAI_SourceData_VM because it is custom made but unfortunately I cannot change indexes on 3rd part vistaVM tables

Kristen · April 19, 2017, 9:15am

If anyone here writes any code with SELECT DISTINCT in it they have to justify it!

I would look into is recoding it so that there are no DUPs in the first place - selecting the DUPs in the first place, then SORTING and De-DUPing is going to add a huge amount of work - especially for that number of columns.

Other general observations (may not have any relevance)

We always include the Schema (probably"dbo."). Supposedly reduces the parse time that SQL takes to check if there is a table for users-current-schema, then when not default to "dbo."

We use SYNONYMS for ALL 3-part and 4-part naming. Means that we only have one place (i.e. the definition of the Synonym) to change the code if the DB (or server in 4-part naming) changes.

I'm sure you know this, but performance will be dreadful because this is not SARGable.

the LEN() and RIGHT() have to be performed in a scan of every possible row. Then I expect the BETWEEN will not use an index-range because of the arithmetic

Plus you also have an implicit cast from String to Number when comparing RIGHT(xxx) with a numeric expression.

I repeat that your best way to solve all this stuff is to create an enquiry database that DOES have all the indexes you want - plus in the process of populating that you could SPLIT things like VStock_strBookletIdent into two separate columns for the LEFT(4) and RIGHT(7) parts, index those columns, and THEN this type of query will fly ...

sapator · April 19, 2017, 9:35am

Hi.
this is a query as part of an sql job that takes about an hour.
I'm not 100% on the Distinct but if i do not use it, i get millions on rows, in comparison of thousands that I get with using the distinct. Haven't look on why it is there, didn't know that could make any difference but the amount of time is about the same, using or not using it.

A second observation , can't understand what you mean by DUP's. I don't know the technical term. Is it duplicate rows?

I think a huge problem (as you pointed out) I have pinpoint is this:

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

		)

Without this part the code will run in seconds, but with it, it will go for a few minutes.
That is exactly the problem I was afraid of. This is ( ) the 3rd party developer tables and we cannot mess with them.
Also I remember spending hours and hours in changing - translating this part of the code (from an older one) so I can have the random and non random vouchers work correctly.
I'm really afraid on touching this again (as I'm not a an expert on SQL, let alone on these conversions) and I'm not sure how I can make this better without using index or split the columns as you suggested (?)

thanks

Kristen · April 19, 2017, 11:02am

So code it so that there are no duplicates - e.g. change the JOINs that currently match multiple rows so that only match a single, representative, row.

Apologies, yes "DUPs" was my abbreviation for "duplicate rows".

This is why I think you should consider pulling all the 3rd party database into your "Enquiry database".

maybe just try it with a couple of tables (the ones used in this query) and see how your revised query peeforms:

INSERT INTO MyEnquiryDB.dbo.tblStock
SELECT *,
    [VStock_strBookletIdent_LEFT] = LEFT(VStock_strBookletIdent,4),
    [VStock_strBookletIdent_RIGHT = CONVERT(numeric(99,9), RIGHT(VStock_strBookletIdent,7)),
    ... etc ...
FROM VISTAVM.DBO.tblStock

and then create an index (this is just an suggested example, not a real index create statement )

CREATE INDEX MyIndexName ON MyEnquiryDB.dbo.tblStock
(
    VStock_strBookletIdent_LEFT
    , ...
)

Assuming that goes well then your task will change to become the best possible way of "copying" data from the 3rd party database to your Enquiry database (e.g. if you need your enquiry database to be "no more than 1 minute old". Obviously if your enquiry database can be "24 hours old" that problem would be much easier to solve!)

sapator · April 20, 2017, 8:37am

Hmm.
OK but this query is part of the night time job.
So this is actually a part of the Enquiry database.
So if I do that I would actually create a Enquiry database for the Enquiry database
Let alone that I would actually put my hands on a tested and working database that communicates with our database centers, local databases, sap and reporting.

Currently I am trying an alternative but I would appreciate any help here, or if this is possible at all.
I'm inserting data to a temp table like so:

declare @date as datetime
   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 as X,
	[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]
	,TransT_strBarcodeRedemp,TransT_strVoucherBarcode
	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

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


--)


----------So far so good I get the data in 2 second.
--------Below is the second part that I join the tables again.
--------How would I go below (if possible). Can i split the data somehow?


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],
	COI.[lClientOrderID]
	[IsComplimentary],[Quantity],[TransI_intFeatureSeq],[InitialSalesChannel],[Session_dtmRealShow] from #tempB
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


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


)



drop table #tempB

So I'm using the temp table to insert the data before I start the checks for the barcodes.
I was wondering if (and how) is possible to have the #temp table, already split the barcodes, as I would have to do per your suggestion on the Enquiry database . So I could have a, let's say, on the fly Enquiry database.
And then I could do the calculations (or at least use a view but I think I cannot create indexes as there are some settings in the database the prevent ndexing on my views).

Is this possible or again the barcode checks will slow down everything, no matter what?

Any suggestions?

Thanks.

sapator · April 24, 2017, 1:10pm

Hi.
OK I Can't think anything to do without creating a new dut I didn't get a go for that so I'm trying this "Please help on conditional join" that obviously will not work as I must check on every row of tblClientOrderItem.
Never mind, I wanted to ask about this conversation. Isn't using JOIN VISTAVM.DBO.tblClientOrderItem COI ON ... AND (((LEN(TransT_strBarcodeRedemp)=20 ... AND RIGHT(TransT_strBarcodeRedemp,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1)
will have to check each value on tblClientOrderItem?
I mean even if i fix the indexes, wont' this have to compare each tblClientOrderItem value thus comparing millions of rows?
I just can't grasp this.
thanks.

Kristen · April 24, 2017, 1:57pm

Yup.

But if you created a warehouse table or similar, and physically split that data into separate columns, and index them correctly, then your life would be rosy

That's not going to happen whilst this data is housed in a 3rd party proprietary database.

sapator · April 24, 2017, 2:01pm

I know
I don't know, I mean they don't gave an OK for the database.
...

thanks.

Kristen · April 24, 2017, 2:06pm

We have a client who will not pay the Licence fee for people like us to directly query the data in their 3rd Pary DB (its a lot of $$).

So they are allowed, within their licence, to Export the data ... so they export all the data, copy all the files, we import the files, "throw away" 90% of the data, and then merge the new, interesting, stuff with our data.

its hundreds of files and GB of data transferred each night ...

... but it was decided this was better, and cheaper (I wonder if it really is!!) than paying for the licence.

So maybe "Export and Re-Import" might be a route for you too?

Can't see that working for "near real time", unless the 3rd Party DB has "ChangeDateTime" columns on all the tables you are interested in ... here's hoping

sapator · April 24, 2017, 2:23pm

They don't have change date time in the particular tables.
This table just hold every voucher rate and only insertions will change it.

Well if inserting to a new database is the only way, perhaps I will re discuss.
So let me get this straight.
the plan is to insert the data to parametric tables, correct?
Now the issue here is that the night backup does that (as I've said this query is part of the night backup).
So the problem here is actually the night backup.
So should I spit the columns on VStock_strBookletIden etc?
OK but how about the comparison of tblClientOrderItem?
What I mean is that if all the trouble is to get something, AFTER the night backup then there is no need to do that.
The problem is to make the night backup go faster.So if we already, for one time, we need to compare with the million rows, then, that's it, we do not need anything else.So indexing after that is kinda of not a need (at least not my primary concern right now)

Kristen · April 24, 2017, 4:12pm

IF there are only insertions and IF the PKey is ascending (such as an IDENTITY) then you can just import "GREATER THAN" MAX(ID) you already have

If you have a RESTORE from BACKUP as part of the overnight routine, then you could just run a JOB to create the split-columns after the RESTORE.

(I thought from other messages that you needed "near real time" reporting, but if last night's RESTORE version is good enough then I agree, no need to pull "new" data during the day.)

You could make a new table, with the same PKey as the APP table, and store the "split columns" in there. Then JOIN from that table when you query those columns. It might be better to have all columns (i.e. both the original table, and the split-columns) in a single table so that you don't have to JOIN the extra table, but some testing would discover if that made a big difference to Query speed.

So presumably not just a RESTORE but some ETL processing?

If you are doing that every night on the WHOLE data, even for unchanged rows, then perhaps it could be speeded up by ONLY doing it on changed rows? Obviously that only works if the changed-rows is a small percentage.

On tables that have no ChangeDateTime columns we add one, and compare every row, and every column, against the master copy and set the ChangeDateTime if it is different. Generally we can use some other strategies too so we do not have to compare every-row, every-night, but that depends a lot on what the 3rd party database actually does - e.g. if a Date is added as part of processing work-flow then the presence of that date can be used to find the "easy" changes - you still have to find the hard changes. Maybe there is an Archived flag, so you don't need to check them (or maybe only check them on Sundays). And so on.

sapator · April 25, 2017, 7:52am

Hi.
OK let's say I split the column, how would i get rid of the between?Or it won't make a difference on performance?
I just had a look on the 5000 lines thing we are using and I got the goosebumps.
If I have to do the splitting I must be extra cautious because this is live accounting IRS data that I don't want to get it wrong.

So the general attempt would be to
A)Split the columns using AND (((LEN(TransT_strBarcodeRedemp)=20 and RIGHT(TransT_strBarcodeRedemp,7)
and (LEN(TransT_strBarcodeRedemp)<>20 and RIGHT(VStock_strBookletIdent,7) .
Won't this slow down the night query anyhow?
How would I split the columns in a fast way?
with a CASE?
B) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1))
this is supposed to do a compare on the split columns(?) won't this impact performance?

Thanks.

sapator · April 26, 2017, 7:13am

Hi.
As a first step we are removing the data till one month before on the backup DB.
We had it on 3 months ago with 2,5 million rows but we did not need data as back as 3 months.
So I'm calculating about 800.000 to 1 million rows.
The query is run weekly but we missed this run as it took place yesterday.

sapator · May 3, 2017, 8:13am

Hey.
Today i got the new numbers as the weekly query run.
I got from 21 minutes down to 5 minutes.
I guess because the server is still adequate, and has free rows it runs more smoothly now but I'm keeping the column split idea that, unfortunately, i did not get a go for it right now from the management.

Kristen · May 3, 2017, 12:08pm

IMHO (and it seems also in the opinion of others here over the last few weeks that you have been asking these questions) you need an enquiry version of the database to prevent catastrophic loading on your network.

sapator · May 3, 2017, 12:47pm

You are correct but I have to get permission to start and currently this is not an option.
However I'm pushing such as we can start crating an entirely new version that will load the data from all the server to the accounting SAP database.
So then I would appreciate any help as this would be a big project and I would like to do this properly.
Till then, I'm stuck with those issues I'm mentioning.
Thanks everyone for your patience

sapator · May 15, 2017, 1:45pm

OK.
Just a heads up, I got permission to build a new Accounting db the next few months.
This will bind our custom DB with the night backup to our SapBridge database in order to import at SAP.
Currently I'm lost as the old procedure is about 5000 rows and we have a lot of customization's in extra tables that I'm discovering from a first look.
So this will be in small steps.
I will post if i need any help but the initial tables do not need the tblClientOrderItem table so currently I do not need anything on that particular field.

So to sum this up. Currently I'm exploring the old import process and when I will be able to start with a small sample and need help on indexing correctly, I will post back or in a new thread.

Thanks.