Help! dynamic filter in-DB

[ascovell]

Hi all,

I think there must be a better way to achieve what we are doing, but struggling to implement it right now:

This is the start of our process - what it is basically doing is isolating any new records in two SQL tables before they are sent through other processes.  But i think it is extremely in-effecient.  It imports the entirety of both tables and then de-dupes against already processed records stored in an xydb file.

What i need i think is to use a dynamic in-db filter to only extract the data needed.

Just been getting my head round batch macros and have built this:

Which i think is doing what i need (working out what data it needs and then updating the filter to select that data).  However this is not in-db which means i still need to extract the whole table, which is what i am trying to avoid.

I'm a bit baffled as to what the in-db macro in/out is and does - i think if i could do the above, but for in-db, that would probably solve my issue.

Any suggestions would be very gratefully received!

[Inactive User · Accepted solution]

From what I see you are taking 2 DB tables and joining them to records from a YXDB, and taking the records that do not match in the YXDB file (ie the new records). If you store this YXDB as a DB table you can take the records and do a left outer join to each of these tables. Then add a filter tool after that and set the filter to require one of the fields from the YXDB to be not-null. Then stream out.

Alternatively you could add a field to your DB tables such as last modified date or processed flag and do filters based on that. 

[ascovell]

Brilliant - thanks very much.

I had looked at in-db join before but had never noticed that you could change the join type. 

a processed flag would be even more efficient i think as per your suggestion, but i need to wait for a colleagues assistance with that.

Here is the new solution:

(processed.xydb is the same as the 'events' DB). 

18 seconds vs over 3 mins previously :)  :)  :)