Don't have anything against a generic solution! Not me :-) I'm one hundred percent for using generic code. I started off using MaxFrame to manage the server side data (SPT). For performance reasons we have been slowly replacing that code (which is good) with our own utility (still generic) because we throw a lot of data at the server and every bit of overhead counts.

When you have a SPT solution and you use complex queries then by definition your solution will not be generic across databases. That's why it's called SQL Pass Through :-) At least as long as there are differences between SQL implementations. I've never tried to use views for complex statistics but I can't imagine that it would work very well...I mean if I have trouble getting MY SQL to optimize correctly what chance does the VFP view?


>I partially agree with you. If generic code can solve the problem 90% of the time, then it's worth it. You can then write custom code for the 10% that needs it.
>
>
>>Problematic might still be statistical programs that use complex joins and group bys. Optimization of selects between the different databases can be very different. A minor change in a select can easily bring a tenfold increase in speed.
>>
>>Locking on the servers can also depend on the exact process plan that the SQL database engine uses to select the data. e.g. a select that causes no locking problems in MS SQL may lock tables in MySQL.
>>
>>If you are working with large amounts of data I believe that you either need to custom code the selects OR use simple selects that bring all the data locally and then use VFP to do the fancy joins and group by(s).
>>
>>Bringing all the data locally is my advice if possible:
>>
>>

sqlexec(m.gnHdl,"select field1... from transactions","transactions")
>>sqlexec(m.gnHdl,"select * from accounts","accounts")
>>sqlexec(m.gnHdl,"select * from promotions","promotions")
>>select ;
>>  ...
>>join ...
>>group by ...
>>having ...
>>

>>
>> It might not be possible if
>>a) the resulting local tables are larger than 2 GB
>>b) you have complex queries that must run very fast
>>
>>Hope that helps