What do you all consider the most efficient way to process through large amounts of data?



1) Load the BO with all data upfront and then "filter", modify data, move on

2) Load the BO upfront and then "seek" (can you seek for more than one record even?)

3) Load the BO with only the small subset you want to work with, inside a loop (many calls to the database), modify data, move on

4) Don't use a BO at all, use ADO or LINQ to SQL.

5) Other



I have to adjust pricing for a very large volume of records, needing to update or insert 1-6 at a time for a given combination of criteria from a total record count of about 200,000.



Just curious as to your opinions or past experience with this. I know I find myself having to worry about the most efficient way of doing things since I never have been able to make the .NET/SQL stuff as fast as VFP.

Keith Chisarik

Ah, I forgot that you are living the love with DB2.



I'd say that you will likely be be better served using DB2 to do the heavy lifting. It appears the DB2 has the equivalent of CLR stored procedures and they appear to be able to be written in VB.NET or C# too! It also has normal SQL sprocs too. The nice thing about the compiled sprocs is that they are efficient at managing complex rules.



Now, the next question is are these files and who processes them?



If the user will be indicating the files to process, then I might try to load the files locally into a BO (doing the parsing along the way), then load that data into a table on DB2, then call the sproc to do the work.



If the files are in some central repository, you could theoretically get DB2 to do the file parsing work also.



If it takes a while to do the processing, you could of course use another thread.



Just some ideas.



Good luck!