Advantage Database Server ignores VFP generated Index

[Former Member]

I've a problem where ADS seems to ignore an index. We have a big solution written in Visual Foxpro 9.0 which works with a VFP-Database (DBF and CDX). We want to use ADS for new extentions written in DotNet. Both the old VFP-App and the new one should work side by side on the same database.

The problem is that ADS ignores the indexes in the CDX-Files in all select statements which results in a horrible performance.

Environment: ADS 12.0.0.0 (also tested in 11.10.0.22, in ADS-Versions than 10.x the issue dosn't appear)

For a test I 've created an Testtable in VFP with the command

set collate to "MACHINE"

create table D:\Temp\TestADS\test (F1 C(10), F2 C(100))

index on F1 tag F1

for lnx = 1 to 1000000

    insert into test (F1, F2) values (transform(lnx), "the quick brown fox jumps ... "

endfor

I then used Advantage Data Architect, created a connection to the testfolder (local server, TableType:VFP, LockingMode:compatible, collation:machine_vfp_bin_1252) and used the SQL-Utility with the following query:

select  F1, F2 from test where F1 = '1000'

The query took 420 ms and the Execution Plan says it is using an "Table Scan" with the warning:

Restriction is not optimized.

Unoptimized Part : F1="1000"

I then use Advantage Data Architect, open the table and select "Re-Index".

Now the same query tooks 4 ms and the Execution Plan says "AOF Scan" without any warning.

It seems, that ADS ignores the index as long as it is not created by himself.

If I fire a reindex in VFP the query is unoptimized again...

Can someone give me a hint or is it a bug?

[former_member1025521]

Hi. I have the same problem. Do you find a solution?

[Former Member]

Unfortunately not. I find out, that the issue appears only if you use set-based commands like "select". If you use the ISAM-commands like AdsExtendedReader.Seek() where you can set the index explicit (with AdsExtendedReader.ActiveIndex), the the index is used and the command is very quick.

Today we are using this workarround but we are wery unhappy with it because we had to rewrite a lot of our data-access-logic.

[former_member541656]

Hello Jens,

Currently we are working on the same issue, this with the notice that this counts the same for ADT's/ADI's.

I find out, that the issue appears only if you use set-based commands like "select". If you use the ISAM-commands like AdsExtendedReader.Seek() where you can set the index explicit

Maybe, at this moment, I'm only posting because the emphasis I put to your quote above and the hint you gave me by this seemingly unimportant piece of text. So it looks like when "we" build the index ourselves, the DD does not know it is there (for Selects and such).

in ADS-Versions than 10.x the issue dosn't appear

Interesting, because we use 10.0.0.28 (for the server) and there this thus does not work for the ADT's.

When we find a solution, I will post it here.

Regards,

Peter