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

No filter, sort, default values, nothing you mentioned.



It is a very simple BO with a single fill all method.



The 109 represents seconds and just represents a very small portion of the operation for illustration purposes.



I could do 87k adds to a cursor or table in VFP in the blink of an eye. I must be doing something wrong here.



Could it be the fact that I am using DB2 in some way, I wouldn't think so since I am just doing the add in memory but at this point I am grasping. I am going to build a sample project with the same table structure/BO properties using SQL and see what happens.




Keith Chisarik

Keith,

I guess without getting my hands on it I cannot give you any more suggestions here.  For whatever it is worth, we ran a test today dealing with 400,000 records (medical transactions) on a combination of tables that may have as many as 50 fields.  We got the calculations and response down to under 1 second.  I know that this doesn't help you with your problem, but we have all been laughing around here lately because everything that we are doing was literally impossible with VFP....because we tried!  Even DB2 is a better database than VFP and can perform at a much better level simply due to the fact that it is a server, supports sprocs, doesn't stream the entire file over the network, etc.

The Add alone is not going to slow the world down.  To prove this, create a quick sample project that uses the SF sample database.  Then using the customers BO, add 80,000+ records dynamically (without committing them back to the server) to see what type of response that you get.  I am willing to bet that it will FAR exceed 109 seconds.  Also, if you are not needing to update the UI, use the NewRow method instead as this will not attempt to update any bounds controls...which can improve performance as well.  At this point the database is irrelevant since you are dealing with the BO alone.