incremental reload still slow...any tip?

[#1]

Im doing an Incremental Loading but it still taking too much time...

here is the code:

concatenate([PeriodoAActualizar])

Load

   [MATNR] as [Número Material],

   [VBELN] as [Código Sales],

   [POSNR] as [Pos Sales],

   [PSTYV] as [Tipo Posicion],

   [SPART] as [Sector],

   [AUDAT] as [Fecha Pedido],

   [ERZET] as [Hora Pedido],

[CMFRE] as [Fecha Lib. Cr y Cc],

   [KUNNR] as [Número Cliente],

   [BZIRK] as [Región Cliente],  

   [VKGRP] as [Código Vendedor],  

   [KWMENG] as [Cant. Entrega Req.],

   [LFGSA] as [codStatus],

   [BRSCH] as [Código Ramo],

   [ABGRU] as [Cod.Mot.Recha],

   [AUART] as [Subtipo Doc.Ventas],

   [WAERK] as [Moneda doc.Pedido],

   [NETWR] as [Valor neto Pedido],

   [STCUR] as [Tipo Cambio Pedido],

   [VRKME] as [Unidad Pedido Venta],

   [UMVKN] as [DenominadorDivisionQtyPed],

   [LGORT] as [Almacén],

   [KNUMV] as [Cod Condición Descuento],

   [ZLSCH] as [ViadePago],

   [AUGRU] as [Código Motivo Pedido],

   [ERNAM] as [CreadoPor],

   [PLTYP] as [ListaPrecio]

   WHERE DATE([AUDAT]) >= DATE('$(v_UltimaFechaIncremental)');

SQL Select VBAP~VBELN VBAP~POSNR VBAP~KWMENG VBAP~VRKME VBAP~PSTYV

	VBAK~SPART VBAK~KUNNR VBAK~VKGRP VBAP~MATNR VBKD~BZIRK KNA1~BRSCH VBAK~KNUMV
	VBUP~LFGSA VBAK~AUDAT VBAK~ERZET VBAK~CMFRE VBAP~ABGRU VBAK~AUART
	VBAP~WAERK VBAP~NETWR VBAP~STCUR VBAP~UMVKN VBAP~LGORT
	VBAK~AUGRU VBKD~ZLSCH VBAK~ERNAM VBKD~PLTYP from VBAP

JOIN VBAK ON VBAK~VBELN = VBAP~VBELN

JOIN VBKD ON VBAK~VBELN = VBKD~VBELN

JOIN KNA1 ON KNA1~KUNNR = VBAK~KUNNR

JOIN VBUP ON VBAP~VBELN = VBUP~VBELN AND VBAP~POSNR = VBUP~POSNR

WHERE VBKD~POSNR = '000000';

[swuehl]

Seems like you SQL statement is not using a where condition that includes your date, so your database will deliver all the data, and only your QV client will limit the records using the Date in the preceding where clause.

Try including the date condition in the SQL where clause.

[#3]

swuehl...it is a "Where" sql statement in in the code:

WHERE DATE([AUDAT]) >= DATE('$(v_UltimaFechaIncremental)');

it works, loading only the records after that date, but take the same time having or not having the WHERE statement.

[swuehl]

That's a WHERE clause indeed, but not executed by your DB, but by QV client. The DB is only executing the part after SQL SELECT ...;

Thus the load on the DB is the same, with or without

WHERE DATE([AUDAT]) >= DATE('$(v_UltimaFechaIncremental)');

You should limit the records on the DB itself to gain really gain from the incremental load.

[#5]

So where I put the "WHERE" statement?

its give me error if a put the statement in the SQL that loads the DB.

OpenStream failed. Key = SQL_ERROR ( Number:000) for statement:

SELECT VBAP~VBELN VBAP~POSNR VBAP~KWMENG VBAP~VRKME VBAP~PSTYV VBAK~SPART VBAK~KUNNR VBAK~VKGRP VBAP~MATNR VBKD~BZIRK KNA1~BRSCH VBAK~KNUMV VBUP~LFGSA VBAK~AUDAT VBAK~ERZET VBAK~CMFRE VBAP~ABGRU VBAK~AUART VBAP~WAERK VBAP~NETWR VBAP~STCUR VBAP~UMVKN VBAP~LGORT VBAK~AUGRU VBKD~ZLSCH VBAK~ERNAM VBKD~PLTYP FROM VBAP WHERE DATE( [AUDAT] ) >= DATE( '01/5/2013' ) JOIN VBAK ON VBAK~VBELN = VBAP~VBELN JOIN VBKD ON VBAK~VBELN = VBKD~VBELN JOIN KNA1 ON KNA1~KUNNR = VBAK~KUNNR JOIN VBUP ON VBAP~VBELN = VBUP~VBELN AND VBAP~POSNR = VBUP~POSNR WHERE VBKD~POSNR = '000000'

Same if I use in this format: WHERE DATE( [VBAK~AUDAT] )

And if I put in the end before :

WHERE VBKD~POSNR = '000000'

[swuehl]

Well, it's SQL and syntax might differ from DBMS to DBMS, so I would advise to get help from your DB admin.

It might look similar to this (at the very end of your script):

WHERE

VBKD~POSNR = '000000'

AND

VBAK~AUDAT >= '$(v_UltimaFechaIncremental)';

Again, get help from someone who knows your DB on this.