Ok, here's my 2 cents:

I believe if it can be done in SQL, then it SHOULD be done in SQL. I had a

time trying to get an xbase algorithm to work recently, and eventually just

wrote simple SQL to do the same thing. My SQL code was 3 pages long, whereas

the xbase code was over 9 pages long. I think the SQL was easier in this

instance, but of course, it all depends on what's called for in the spec.

Now I suppose someone might prefer to write tons of code if they read a

ComputerWorld article some months ago (I wanted to reference it but I couldn't

find it!) that said "...programmers should get paid for the number of lines of

code they write..." or some other crap like that. That paper article deserved

to be in the restroom to be used for a better purpose, like toilet paper,

although that might have been too good for it. <g>

Someone else (I think Cindy Winegarden) really hit the nail on the head when

she said how it is portable and not Foxpro dependent. That is SO true. And

if for some reason you're in a spot like Matt, you can take your SQL knowledge

and apply for those many Oracle or other jobs out there where SQL is used.

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

I wonder if Anders is available for hire since he is an SQL Guru.

Chad Bourque

U.S. Bankruptcy Court - 11th Circuit

Alabama - Northern District - Southern Division

Chad@teche.net

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

----- Original Message -----

From: Michael Babcock <mbabcock@kepro.org>

To: Multiple recipients of ProFox <profox@leafe.com>

Sent: Wednesday, October 06, 1999 9:55 AM

Subject: CHATTER: Re: SQL in plain English -Reply

Ok, here's my 2 cents:

I believe if it can be done in SQL, then it SHOULD be done in SQL. I had a

time trying to get an xbase algorithm to work recently, and eventually just

wrote simple SQL to do the same thing. My SQL code was 3 pages long,

whereas

the xbase code was over 9 pages long. I think the SQL was easier in this

instance, but of course, it all depends on what's called for in the spec.

Now I suppose someone might prefer to write tons of code if they read a

ComputerWorld article some months ago (I wanted to reference it but I

couldn't

find it!) that said "...programmers should get paid for the number of lines

of

code they write..." or some other crap like that. That paper article

deserved

to be in the restroom to be used for a better purpose, like toilet paper,

although that might have been too good for it. <g>

Someone else (I think Cindy Winegarden) really hit the nail on the head when

she said how it is portable and not Foxpro dependent. That is SO true. And

if for some reason you're in a spot like Matt, you can take your SQL

knowledge

and apply for those many Oracle or other jobs out there where SQL is used.

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>

>Ok, here's my 2 cents:

>

>I believe if it can be done in SQL, then it SHOULD be done in SQL. I had a

>time trying to get an xbase algorithm to work recently, and eventually just

>wrote simple SQL to do the same thing. My SQL code was 3 pages long, whereas

>the xbase code was over 9 pages long. I think the SQL was easier in this

>instance, but of course, it all depends on what's called for in the spec.

OTOH, I've had code that I could have written in SQL, but it was much

easier in xBASE. It depends what you are coding. xBASE is record-oriented

and SQL is set-oriented.

I've taken to grabbing sets in SQL and then manipulating them with

xBASE. It would be more difficult (though not impossible) to do it all in

xBASE. I can't see how I would do it all in SQL. There are too many bits

that need to be done. Selecting the records is only the start. There are

many checks after.

>Now I suppose someone might prefer to write tons of code if they read a

>ComputerWorld article some months ago (I wanted to reference it but I couldn't

>find it!) that said "...programmers should get paid for the number of lines of

>code they write..." or some other crap like that. That paper article deserved

>to be in the restroom to be used for a better purpose, like toilet paper,

>although that might have been too good for it. <g>

And if you spent the day shortening a program to make it run better? I

agree. The article sounds quite nonsensical.

I prefer to write compact code. Whether that is xBASE or SQL or some

mix depends on what I need. Here's one for the SQLers:

I have several DE tables where the primary key could be changed (and my

boss wants this). My xBASE code has a check for this causing a duplicate

key. I simply check for the new key and if the recno() is of the record

being edited, it's OK, otherwise the key already exists and it's an error.

(This method does assume that there aren't already dups in the table.) What

is the SQL solution? How long is it? How *clear* is it?

>Someone else (I think Cindy Winegarden) really hit the nail on the head when

>she said how it is portable and not Foxpro dependent. That is SO true. And

>if for some reason you're in a spot like Matt, you can take your SQL knowledge

>and apply for those many Oracle or other jobs out there where SQL is used.

SQL is not standard enough that the transition is going to be 100%

smooth. I follow comp.databases and comp.databases.theory and I see it

quite often.

I am not wedded to xBASE or SQL. I use what will accomplish the job.

"xBASE" is not a swear word in my books.

[snipped previous]

Sincerely,

Gene Wirchenko

genew@shuswap.net

Computerese Irregular Verb Conjugation:

I have preferences.

You have biases.

He/She has prejudices.

Chad

You don't need composite indexes. An index on each or most of the columns

referenced in thew WHERE clause will work fine if you AND the different

search conditions.

-Anders

----- Original Message -----

From: Bourque, Chad <Chad@teche.net>

To: Multiple recipients of ProFox <profox@leafe.com>

Sent: onsdag den 6 oktober 1999 17:28

Subject: Re: CHATTER: Re: SQL in plain English -Reply

| 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