What is the fastest way to update a large amount of data into a VistaDb table?
Lets say I have 10,000 rows of data and I'm updating 5 fields based on the primary key field. Is it faster to run 10,000 sql update commands or would it be faster to build a new table and load the 10,000 rows via DDA and then do a query to merge the fields to the existing table?
I'd hazard a guess if you set up DDA to use an index for the Find then that's going to be the fastest way as it's very close to what happens under the covers in the SQL cases. You should run an experiment for your situation to see.
Thanks for the information.
What if I'm trying to update the same 10,000 row table. Is it faster to use DDA table.find to find the row based on the primary key and update that row or is it faster to load the data into a temp table (via DDA) and then use a query to update the original table?
The fastest way is likely to use DDA to directly update the 10,000 rows. The second fastest way is likely to open a connection, create a sql command, and call that command in a loop. In either cases the best way to boost performance is to open the database in exclusive mode - that eliminates a lot of locks the database would otherwise do.