Chris,

I agree with your assessment of Stored Procs. I just wanted to add (for the benefit of others) that using SP's for all data access in a client/server application is considered a "best practice" methodology.

~~Bonnie



>Walter,
>
>>I´ve been busy the last week in optimizing a routine that mungs data from a dozen tables into one. There is a lot of calculation arround there. The calculation however was nothing compared to get the data from the database (either DBFS or SQL server) through SPT or views. The performance on a slow network (10 Mb) on a 700 Mhz machine was dissapointing. I could not optimize the data retrieval with SQL either with VFP or SQL server. For the VFP version of the server I reverted to the technique of SETting ORDER, SEEKing and SCAN WHILE. The data retrieval was about 10 times as fast. I´m still buffled about the performance difference (something I need to find out). This Illustrates once again that you have so many ways in VFP to skin the cat.
>
>
>Im only going to pick on one of your points (and amazingly it isn't a plus to either VFP or .Net so doesn't impinge on your conversation with Rick which I am finding very informative).
>
>I just would like to inform you that one of those ways to skin a cat in either VFP or .Net is called "Stored Procedures" (I assume you know about these but I am being clear for anybody in the audience who might not know about them and thinks using the DML in VFP is the only way to work with data and may want to research the concept)
>
>I am highly confident that in your described circumstance of pulling data from 12 tables and merging it into one and doing calculations on it, it may have performed even better by using a stored procedure - We had a routine that we had written in VFP that had to get data from 7 or 8 tables and perform calculations and do other stuff, but the fastest we could get the code to run (no matter VFP or SQL data) was about 2-3 hours on a P4 with 512 MB RAM (this was processing about 100k - 200k records in the largest table) - we tried everything, INDEXSEEKing, SETing ORDER, LOCATE FORs, I mean EVERYTHING VFP had we tried but still it ran only taking at least 2-3 hours (even DAYS in some circumstances). Then we tried it in a Stored proc on SQL Server - exact same result, but takes only 15-20 minutes - so now the VFP backend version of our app uses the old code but the SQL backend version uses the stored proc. So it might be something to look into in your current situation?
>
>I also noticed in a previous post of yours in this conversation that you were worried about server performance issues which is why you try to do as much data processing on the client as possible, but in our experience (we have a number of these monster stored procs on our SQL backend) a SQL (or Oracle) database server is usually so overpowered that even the most demanding of stored procs or queries still only makes a tiny dent in the available performance of the server. Another benefit of using Stored Procs is that all the processing code we have written in the SQL backend is still perfectly compatible with any new frontend we write (we wrote a .Net client program that makes use of some of these stored procs through ADO.Net without trouble)
>
>
>So maybe stored procedures is something that you might want to look at?
>
>Chris.