Rushmore & BROWSE vs SQL SELECT

[StewartUK]

Using VFP9 SP1

I'm confused. I have an index on a table where the expression is

TRIM(AREA)+TRIM(DISTRICT)+" "+TRIM(SECTOR)+TRIM(GROUPING)

When I execute browse statement as follows:

BROWSE LAST FOR TRIM(AREA)+TRIM(DISTRICT)+" "+TRIM(SECTOR)+TRIM(GROUPING) = [BN12 6AY]

the data appears instantly.

When I run a SQL SELECT like this:

SELECT PKEY,LINE1 FROM ADDRESS WHERE TRIM(AREA)+TRIM(DISTRICT)+" "+TRIM(SECTOR)+TRIM(GROUPING) = [BN12 6AY]

it takes over 2½ seconds.

Why the difference? Is there some way I can use the index with a SQL statement?

Thanks,

Stewart

 

[alvechurchdata]

Is that first query creating a new work area or just showing a filtered list from the table that's already open?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[StewartUK]

Geoff,

Er, if we're looking at the same thing, the first query is actually a BROWSE.

Stewart

 

[alvechurchdata]

Er, if we're looking at the same thing

Sorry. It's the Simpson gene cutting in again.

You say that the Browse appears immediately. It's only showing a few dozen records - how long to get to the last record?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[StewartUK]

Hi Geoff,

Thanks. Well the BROWSE & SELECT above only shows/finds 3 records, but FWIW the cursor moves down instantly in the BROWSE.

I have the same issue using index expression TRIM(AREA)+DISTRICT which shows/finds 202 results. In the browse window, Ctrl+End gets me the last record almost instantly.

Stewart

 

[Dan Freeman]

One other thing: index expressions must evaluate to a consistent length so VFP will pad any expression using Trim(). That index may not be getting you what you think.

It's not a good idea to use Trim() in index expressions.