Incremental Load is not more efficient

[roisolberg]

Hi,

in order to speed up my DB mirroring I've tried to switch to an incremental load on our heavier tables but it doesn't seem to be more efficient (at least not by the loading time)

(some data was edited for privacy)

so first i am preparing the Last modified variables (1 for each table, so about 20 variables)

Temp_LastModified:
Load Distinct Max( LASTMODIFY) as LastModified
from $(vSysDB)ORDERS.qvd] (qvd);

let vMax_ORDER= Date(peek('LastModified',0,'Temp_LastModified'),'YYYY-MM-DD hh:mm:ss');
drop table Temp_LastModified;

..

then,

For Each DBName in 'Company1','Company2','Company3','Company4' ,'Company5', 'Company6'

ORDERS:
Load *,
'$(DBName)' as Company_Orders,
ORD& '|' & '$(DBName)' as Key_ORD_Company
;
SQL SELECT *,
system.dbo.tabula_dateconvert($(DBName).dbo.ORDERS.UDATE) AS LASTMODIFY

FROM $(DBName).dbo.TODOLIST
Where system.dbo.tabula_dateconvert($(DBName).dbo.ORDERS.UDATE)>= CONVERT(DATETIME, '$(vMax_ORDER)');

..

..

and finally concatenating the previous loaded qvds and saving by another loop.

so i guess i have 2 questions:

1. how do i check my efficiency ? i reckon there is a better way then loading time (that also varies from load to load)

it seems that adding the variables/where on the select/ concatenating even though it is lighter it has more steps and becomes kind of the same..

2. this script is loading incremental but maybe there is something i am doing wrong?

3. Is it the Load on top of the Select? (didn't find any info about this)

Thanks,

Roi

[Or]

It's hard to give a generic answer. Typically the time saved with an incremental load is on the SQL / data source end, as you don't have to return the entire history each time. However, this depends on the partial queries actually being faster than the full queries, which in itself depends on the query, table indexes, and related factors. You can cross-check this part by running both the full load query and the partial load query individually and comparing times.

[roisolberg]

Thanks for your replay,

Yeah i've tried to cross-check and compare times and i found out it wasn't a significant improvement as i had expected.. but i get your point, it might be lighter on the DB and that's those points id like to compare but not sure how..

[Or · Accepted solution]

You'll probably want to take that up with your DBA or someone who specializes in query performance, as it's not actually a Qlik-related issue. As a starting point, you should probably check if there's an index available on the table you're reading from which matches the condition you're applying. If there isn't, perhaps one could be added, or perhaps you could modify your condition to use an existing index.