There is really on ONE best way...especially if you are going to be pulling from a number of different tables and it may require a number of different queries....a CLR stored procedure.  This let's you still program in .NET, but the work takes place at the server.  However, this will only work for SQL Server.  Otherwise you will have to create stored procs in the database language you are using.

Here is an example of performance increase, we have a program that deals with entering payments that come back on an EOB.  We released our first blush sitting on top of VFP and it cratered if the EOB was very large....not to mention slow.  We now have this totally on SQL Server and using CLR stored procs and it is basically instantaneous.  We have another scenario where the end-user is posting the claims, and there were literally 18+ queries that took place each time in order to check all the exceptions, etc. to get the correct fees, and so forth.  This totally died in VFP, but by using the CLR stored procs, it is literally as fast as you can type the code....it is incredible!

The last example I will give you relates to our prescription data, there are some tables with millions of records which have to be queried and compared to check for items such as drug-interactions.  Pulling this back to the client would never work, but by using the CLR stored procedure, it is basically as fast as you can type again. 

In the current beta, though there is no documentation yet, there is also a new feature that allows a single query to the server to bring back multiple result sets (assuming the database supports this [i.e. SQL Server]).  I have one example where I have a single query to the server but load 6 business objects...one trip...no stored procs...and FAST!

I know that you are using DB2 in certain conditions, so this is an issue and will definitely require you to "think out of the box."  DB2 is a more cumbersome and more limiting environment to work within.  I am not as familiar with it so I do not know if it supports the multiple result sets or not. 

All of your questions are valid, but trying to determine if ADO or LINQ to SQL will help is actually not the issue here.  First, we use ADO within the BO...secondly, LINQ to SQL is only going to strong-type your queries, and has nothing to do with performance really.  The whole issue with large data is generally getting it back from the server, the number of trips, and how the query is formulated.  Also, different scenarios require different solutions.  This is why I mentioned several options above.

In this case, you shouldn't be bringing all 200,000 records back to your side.  If you know what needs to be changed, you are better off working in a much smaller data set (i.e. the pk, code, and fee) then have the update take place back on the server.  If the end-user has to enter all 200,000 fees, then this is going to happen with one or a few at a time, not all 200,000.  So I would imagine that you already know what needs to be changed, so you could create a CLR stored procedure (or TSQL if you want pain ) and then have all of this work take place on the server so the data never makes it to the client...just the results and the information that the end-user needs.

Whoa...that was a big post, but I hope it give you some ideas

P.S. Using SQL Server versus VFP we have had in some cases 100 times better performance...SQL Server is WAY faster!  It all comes down to how you deal with the data.

LOL...I do it all the time.  Usually we are both posting at the same time when this happens

From your response I get the impression that you believe CLR is the way to go with SQL Server. Is this so and, if it is, what made you guys move away from standard SQL stored procs?

Cheers, Peter

We still use standard TSQL stored procs for INSERT, UPDATE, and DELETEs (our CRUD settings).  But anything past that or that requires customization or custom queries on the server, we use CLR.  We haven't changed any of our standards or logic.  CLR stored procedures are much easier to work within and have more flexiblity (especially for .NET developers).  You get all of the benefits of TSQL (as it relates to server side performance [you do take a slight hit using CLR over TSQL but it is not really noticeable]) but get to write all of your code in C# or VB.NET.  Also, the DDT will deploy CLR assemblies for you as well which just adds to the ease of implementation.

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.

What will happen is records will begin to save on multiple threads, which helps to prevent an update bottleneck when updating large datasets.  This is especially important when updating over a VPN or slow connection.  The worse the bandwith, the more performance gain you will get....but this will still dramatically improve an update time when dealing with so many records.

FWIW...and a little sneak peek...we are setting up a knowledge base that will continuously have new articles, samples, and videos.  There will also be howto's, FAQ, etc.  This will make it SO much easier for us to put out new samples, help information, and the like versus requiring a new build, new help docs, and trying to squeeze more samples into the install.  This will be a more fluid environment, and this would be a good topic that may end up there.