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

[anbu1984]

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;

[tcullinane]

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.

[rustyfishbones]

Unfortunately I don't have timestamp I can use

[rustyfishbones]

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

[ramoncova06]

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'

[markodonovan]

Hi Alan,

Did you get anywhere with this problem ?

It sounds like it might be a common problem.

Thanks

Mark

http://techstuffy.tv

[rustyfishbones]

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

[rwunderlich]

Hi Alan,

What DB platform was that?

-Rob

[rustyfishbones]

Hi Rob,

It's a PROGRESS Database

Regards

Alan