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