Fastest way to go Update/Insert (to Firebird)
Posted: Wed 13 Apr 2016 06:52
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.
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.