topic Performance issue - Check if rows exist in Resident Table in QlikView

Performance issue - Check if rows exist in Resident Table

orital81 — Sun, 08 Jan 2012 09:32:17 GMT

Hi, Im performing an incremental load.

A part of the script requires a test if new records were found.

For this I add the following test:

LOAD 1 as Dummy

Resident $(table)

where RowNo()=0;

LET vNewRecordsExists = If(peek('Dummy')=1,-1,0);

But, too much time is spent on this test, meaning this test has bad performance.

Any suggestion to improve performance of this test will be appreciated.

Thanks in advance.

Ori

Performance issue - Check if rows exist in Resident Table

Sun, 08 Jan 2012 09:57:40 GMT

Dear Ori,

I use "not exist" function to get the insert's in a new data set..

At the below, you can see the code that i use.. First of all, i define a variable which holds last execution date.

First load statement, load the data from an excel sheet or rdbms system.

At where condition i get the data of the last reload date.

Then i reload my existing data which i store it as a qvd file.

Then , concetanete the first load statement with the second one by using the "not exist" function.

LET vLASTRELOAD= NUM(RIGHT(DATE(RELOADTIME()),2))&LEFT(DATE(RELOADTIME()),2)&MID(DATE(RELOADTIME()),4,2);

Directory;

LOAD ID,

DATE,

UPDATE_DATE

FROM INC.xls (biff, embedded labels, table is Sheet1$) WHERE $(vLASTRELOAD)=UPDATE_DATE;

CONCATENATE

Directory;

LOAD ID,

DATE,

UPDATE_DATE

FROM INC.QVD (qvd)

WHERE NOT EXISTS(ID); //INSERT

I hope, it gives you an idea about the processes.

Omer

Performance issue - Check if rows exist in Resident Table

orital81 — Sun, 08 Jan 2012 10:31:26 GMT

Hi Omer

Thanks for the helpful answer.

But What if I could find a fast way to validate new lines existance,

wouldn't it be faster to skip this Concatenation step when no lines are found?

Performance issue - Check if rows exist in Resident Table

Mon, 09 Jan 2012 07:02:59 GMT

Hi Ori,

You can validate it with different ways but in the end, you need to insert the new lines to your existing dataset.

as far as i know that, reading a dataset from qvd and concetentation is not very time consuming processes.

in my example, where condition will be a time consuming event, but no other way to understand which row is the inserted other than comparing timestamp fields.

You may wish to use some other specific change-data capture (CDC) tools to get changes less than a second.