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

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

On 7/26/99 1:01 PM, Michael Copeland supposedly said:

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

>table three.

Looks like you need to check out cross-tabs. There is a check box in

the query designer which triggers crosstab generation, although I don't

remember exactly where it is located in 3.0. Check that out and if you

get stuck, give a holler.

___/

/

__/

/

____/

Ed Leafe

http://leafe.com/

If you set:

SET COLLATE TO GENERAL

and then create your indexes, you can perform case-insensitive searches

in VFP, but note that the default is:

SET COLLATE TO MACHINE

Ê

Therefore, your second para is correct assuming that the index was not

created with the expression UPPER(field) or SET COLLATE TO GENERAL.

Ê

Mike Antonovich

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

From: Bill Arnold [mailto:bill@wjarnold.com]

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

To: Multiple recipients of profox

Subject: RE: VFP3 SQL question

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