Mike, I see that you are using upper/lower case in your example - did you

pay attention to this with your indexes (for Rushmore)?

e.g. If I understand Rushmore clearly, if you operate on a field with

UPPER(field), it is not optimizable ... e.g. the indexes and the expressions

against them have to be identical ...

Another thought: if it turns out to be memo fields are causing the

performance problem, maybe you could replicate the 1st 255 chrs into a fixed

field just for this operation (I know that can lead to other headaches, but

if this performance problem *has* to be solved, and it *is* the memo field

causing it ...)

Bill

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

From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Michael

Copeland

Sent: Monday, July 26, 1999 1:01 PM

To: Multiple recipients of profox

Subject: VFP3 SQL question

All,

(hey, everyone is urged to take a stab at this, but note that this is VFP3

and Outer Joins are simulated...and slower)

I have two tables and need to extract information from both to create

table three.

Examples of the two data tables:

TABLE A (both fields are always populated, ID serves as a key)

ID POLICY

1 111111

2 222222

3 333333

4 444444

5 555555

TABLE B (answer field is a MEMO and may be blank; Question may be

blank, ID is never blank)

ID QUESTION ANSWER

1 1 Bob

1 2

2 1

2 2

3

3 2 Alaska

4 1 Larry

4 2 Texas

5 1 Joe

5 2 Virginia

The desired result:

TABLE C

POLICY CLIENTNAME STATE ID

111111 Bob 1

222222 2

333333 Alaska 3

444444 Larry Texas 4

555555 Joe Virginia 5

Description of TABLE C:

Include one record for ALL Policies from Table A whether there exists a

record in TABLE B or not.

Include only one record for each Policy from Table A.

Include ID and POLICY in each record, but CLIENTNAME (answer to Question

1) and STATE (answer to Q 2) may be blank.

My understanding:

I don't think this can be done without three to four steps. Because this

is VFP3, I am using a simulated OUTER join. To keep this message short, I am

not including code samples, but can do so easily. While the memo field is

sure to contribute to some performance degradation, it is necessary due to

some answers being free-form, unlimited length (such as a COMMENTS field).

The 255 character limit was unacceptable for this application.

Attempts so far:

I've got indexes on all fields. I've tried with and without DELETED tags.

The tables have virtually no deleted records due to a record-recycling

approach I've built in.

The problem:

The above task is taking 3 to 4 minutes over a network with 30 to 40 users

with tables that only have several thousand records. I need to get it down

into the seconds (like 15 to 30).

Question(s):

Can this be done in one step? Is the problem the way the tables are set

up?

HELP!

Michael Copeland

Genesis Group Software

mailto:mike.copeland@genesis-group.net