Fastest way to go Update/Insert (to Firebird)

[kneighbour]

I guess the DB type does not matter, but I generally use Firebird. I am using Delphi XE7.

I have been using the UniLoader to good effect - it is a very good way to insert a lot of records very quickly. The new Batch function also look promising for bulk updates, but I have not used it yet.

My general problem is that I do a lot of transferring of data into tempory tables, which I then manipulate, then transfer to the final table (usually for a report). For some projects, using UniLoader is great. For some though, I need to check if a record already exists in the table, if it does, then edit that record, otherwise do an Insert. This stuff is usually for reports - I am scanning transaction records or something, and building a purchase history or something.

This is all very fine, but the problem is the speed. On even smallish tables (10,000 records) this is awfully slow.

I generally just use UniQuery to do all of this stuff. Since I assume this is a very common requirement. I am wondering is there some fast way to do this with UniDac? Why reinvent the wheel if people smarter than myself have already found a better way?

I am pondering whether to somehow separate my records into two groups. Do a scan of the primary records and see if they exist in the secondary table. Read in all these records and use UniLoader to Insert them. Then run a second pass and for the Updates use the Batch function, or just use Edit/Post. This sounds a fairly complex system, and the overheads might be more than what you gain using the Loader.

I am thinking along the lines of (to find all the Insert records)
UPDATE primary SET flagfield='I' WHERE NOT EXISTS (SELECT field FROM secondary WHERE keyfield=primary.keyfield)

or maybe (to find all the Update records)

UPDATE primary SET flagfield='U' WHERE EXISTS (SELECT field FROM secondary WHERE keyfield=primary.keyfield)

Then SELECT * FROM primary WHERE flagfield='I' ... and then to UniLoader, etc

Bit hard to tell which one would be quicker. But is this looking in the right direction?

I could use CachedUpdates too, I am guessing.

I am not sure how Batch Updates work. As far as I can see, they simply poke in a new value for a field. Not good enough. I need to update the fields. ie add the new total to the old total. Not simply overwrite the old value.

Any suggestions would be welcome.

[ViktorV]

This question does not relate to UniDAC functionality. Please visit dedicated forums to learn more about common principles and aspects of working with DBMS. Please fell free to write us if you have any questions regarding our tools and products.