Incremental Load Issue, not fast enough!

[rustyfishbones]

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

[marcus_sommer]

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

[Peter_Cammaert]

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

[rustyfishbones]

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

[marcus_sommer]

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

[rustyfishbones]

Hi Peter,

The 3 fields are indexed

Regards

Alan

[Peter_Cammaert]

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

[rustyfishbones]

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

[ramoncova06]

have you tried creating the key in your db extraction ?

have you tried with a hash ?

why not use the where not exists ?

[rustyfishbones]

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