Excellent suggestions/advice Chad! (as usual from you <g>)

I should know better than to say anything like ALWAYS. There's ALWAYS an

exception to the rule!! Good point.

>>> "Chad@teche.net" 10/06/99 11:35am >>>

Michael,

Just because it CAN be done in SQL doesn't mean it SHOULD be done in SQL.

For example, I have an app I just had to modify because I first wrote it

using SQL. The SQL-Select took ~ 40 seconds whether it came back with 0, 1,

or 300 records. I changed the code to use this type of process:

Create Cursor ...

IndexSeek(MyCondition)

Scan Rest While MyCondition

Insert Into Cursor ...

EndScan

Using this method causes the time taken to be affected by the number of

records found, but even on the largest result sets, the time is now under ~

4 seconds. Now, part of the reason the SQL lost in this case is because the

Where clause could utilize anywhere from 2 to 7 fields depending on user

input. Although the table has several indexes, it doesn't have composite

indexes for every possibility.

After saying this, please don't think that I'm opposed to SQL. I'm not. In

fact, I use it all over the place and it was my first approach to this

problem. It was only after I learned the way the users would be using the

app (repeated small queries), that I went back and began to look for a way

to speed up processing. So, my final suggestion would be this:

Try both SQL and xBase to see which fits your specific needs in each case.

HTH

Chad Bourque

U.S. Bankruptcy Court - 11th Circuit

Alabama - Northern District - Southern Division

Chad@teche.net

Chad was right on target with his suggestion(s), but I'd like to offer my

own point of view. SQL is great. I love SQL. I want to marry SQL. But

frankly, sometimes SQL stinks and just doesn't work well.

We had a thread recently where the end result matched this one - the

priority here is a) accuracy b) maintainability c) ease of use d)

performance. Obviously, it doesn't matter if the code runs in 3 nanoseconds

if it isn't producing the correct results.

I, like others, have a "hybrid" approach to using SQL where I give up using

the FP tool and start rolling my own.

For easy queries such as pulling from just one table, I write the SQL code

myself directly into the program. No brainer.

When I'm joining two or three tables, I use the Query Builder to get my

code because that way it's easier for me to verify the result set and

change it around to get it "just right".

And, for those really hairy data pulls, I usually have a cascading method

of pulling from each, producing temp files, followed by SCANS and so forth

massaging the data. Sometimes super fancy SQL is just too much for my mind

to deal with.

I discovered the same limitation to SQL performance that Chad did, being

that indexes need to exist on every field in the query to optimize

performance. This happened a few years ago and when I reworked the indexes,

my users saw the performance increase (probably a factor of 20) and thought

I had invented a new time continuum or something - the performance was such

an improvement.

To be honest, portability was never an issue for me because I would

"always" be using VFP, right? Ha! It never occurred to me that I'd want to

compile under a different language. Unfortunately I am not proficient

enough with the SQL standard to be able to avoid being bitten when FP

strays from true SQL.

At 11:50 AM 10/6/99 -0400, you wrote:

>Excellent suggestions/advice Chad! (as usual from you <g>)

>

>I should know better than to say anything like ALWAYS. There's ALWAYS an

>exception to the rule!! Good point.

>

<Chad's way cool message snipped>