topic Re: Incremental Load Issue, not fast enough! in QlikView

Incremental Load Issue, not fast enough!

rustyfishbones — Mon, 27 Apr 2015 14:13:20 GMT

Hi All,

I have a table that I create a key field by concatenating 3 fields to get a unique string

The problem I have is when I use this in a where clause for incremental load, it's very slow.

What is the fastest way to retrieve only the new records from the Database? but using a combination of 3 fields to create a unique ID

So I extract the Data into a qvd, I create a temp table and sort by the unique ID, I find the last record and populate into a variable called vLastRecord

I then attempt to take new data from the database with a where clause like

where a&b&c > '$(vLastRecord)';

It's really slow, Any ideas?

Regards

Alan

Re: Incremental Load Issue, not fast enough!

marcus_sommer — Mon, 27 Apr 2015 14:21:18 GMT

Maybe you could convert your string key to a numeric key with an approach like autonumber or creates a own numeric key like:

([StoreNumber] * pow(10, 4)) + ([BillNumber] * pow(10, 9)) + [Pos] as [UniqueKey]

which worked for me fast in a where not exists([UniqueKey]).

- Marcus

Re: Incremental Load Issue, not fast enough!

Peter_Cammaert — Mon, 27 Apr 2015 14:24:09 GMT

In your RDBMS, define an index on the three fields that together form your key. Or use an existing PK.

Re: Incremental Load Issue, not fast enough!

rustyfishbones — Mon, 27 Apr 2015 14:26:34 GMT

Hi Marcus,

2 of my 3 fields contain text, so I could have

ABD456789

CDP456789

do think I could create a unique number using the transaction number?

Regards

Alan

Re: Incremental Load Issue, not fast enough!

marcus_sommer — Mon, 27 Apr 2015 14:45:33 GMT

If your string-fields have always a certain logic it could work to convert a string-char into a number like:

ord(mid(field, 1, 1)

But to create so an errorless key is difficult and needs some effort and will fail if the number is to big because qv had some difficulties in handling big numbers. If you could create a such key within database, maybe as a simle ID it wille be probably better.

- Marcus

Re: Incremental Load Issue, not fast enough!

rustyfishbones — Mon, 27 Apr 2015 14:49:57 GMT

Hi Peter,

The 3 fields are indexed

Regards

Alan

Re: Incremental Load Issue, not fast enough!

Peter_Cammaert — Mon, 27 Apr 2015 15:00:00 GMT

That's not the same as an index on a composite key.

Re: Incremental Load Issue, not fast enough!

rustyfishbones — Mon, 27 Apr 2015 15:03:17 GMT

Hi Marcus,

The strings will always have the same logic.

I have added ord, to convert the string to a number to see if that helps speed up the Incremental process.

I will let you know how I get on.

Regards

Alan

Re: Incremental Load Issue, not fast enough!

ramoncova06 — Mon, 27 Apr 2015 15:06:47 GMT

have you tried creating the key in your db extraction ?

have you tried with a hash ?

why not use the where not exists ?

Re: Incremental Load Issue, not fast enough!

rustyfishbones — Mon, 27 Apr 2015 15:16:49 GMT

Hi Ramon,

I have created the key in the extraction, I will try using where not exists and see if that speeds up the process

Regards

Alan

Re: Incremental Load Issue, not fast enough!

anbu1984 — Mon, 27 Apr 2015 15:31:34 GMT

Do you have any timestamp field in your source table to indicate when records were inserted/updated in it?

If yes, then store Max(Timestamp) of each run in a QVD or text file and use the same to perform incremental load

Select * From Table Where Timestamp > Max_Timestamp;

Re: Incremental Load Issue, not fast enough!

tcullinane — Mon, 27 Apr 2015 15:42:42 GMT

maybe reduce the complexity of the where cause to just where A > $(variable) (or b or c) depending on what will give the closest to wanted data reduction and deal with the additional information loaded afterwards, obviously cant just concatenate but if this reduces the load time enough the additional processing on it ma be quicker.

Re: Incremental Load Issue, not fast enough!

rustyfishbones — Mon, 27 Apr 2015 16:13:24 GMT

Unfortunately I don't have timestamp I can use

Re: Incremental Load Issue, not fast enough!

rustyfishbones — Mon, 27 Apr 2015 16:14:13 GMT

I tried where not exists, but it still takes too long to get the new records

Re: Incremental Load Issue, not fast enough!

ramoncova06 — Mon, 27 Apr 2015 17:00:10 GMT

in that case I would break into pieces to identify where is the actual issue

how much times does the sql extraction take

how many rows does the sql output ?

how many rows are currently stored in the QVD ?

I have read that you do not have a timestamp, could you create it when building the qvd or is it that the you only want append to you current data new keys and not update the ones you already have ?

have you tried creating a sequence number based on your keys and used applymap on your where ? something like

where applymap(field1&field2&field3, 'No') = 'No'

Re: Incremental Load Issue, not fast enough!

markodonovan — Thu, 28 May 2015 10:42:19 GMT

Hi Alan,

Did you get anywhere with this problem ?

It sounds like it might be a common problem.

Thanks

Mark

http://techstuffy.tv

Re: Incremental Load Issue, not fast enough!

rustyfishbones — Thu, 28 May 2015 15:54:31 GMT

Hi Mark,

Yes I eventually solved it. I needed to link the Database table with another Database Table on the Extraction.

Basically I built the query using the SQL SELECT part and joined my table to another table on common fields that were also Indexed. Indexed is the important part of it, so I basically added the field from the table I did not need to the table I required using 3 common Indexed fields from both tables, and I added a field that was also indexed and was also Unique. This provided me with the speed I needed to use Incremental load.

Sounds confusing but it works really well now.

Thanks

Alan

Re: Incremental Load Issue, not fast enough!

rwunderlich — Thu, 28 May 2015 16:49:07 GMT

Hi Alan,

What DB platform was that?

-Rob

Re: Incremental Load Issue, not fast enough!

rustyfishbones — Thu, 28 May 2015 17:33:29 GMT

Hi Rob,

It's a PROGRESS Database

Regards

Alan