cancel
Showing results for 
Search instead for 
Did you mean: 

Advantage Database Server ignores VFP generated Index

Former Member
0 Kudos

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?

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

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

Former Member
0 Kudos

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.

0 Kudos

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

joachim_drr
Contributor
0 Kudos

ADS ignores the index because it uses a different collation.

Former Member
0 Kudos

Hello Joachim,

I dont understand, why this happens. I'm using Visual Foxpro with all setting in German, Codepage 1252. If I change the collation settings of the connection to the testfolder in ARC to somthing other (example MACHINE_VFP_BIN_437, I get an Error when I try to open the table:

The table has one or more index tags defined using the MACHINE_VFP_BIN_1252 collation, which differs from the active connection's collation of MACHINE_VFP_BIN_437.

So I think the index has the collation MACHINE_VFP_BIN_1252. If I changed the collation of the connection back to that, I can open the table without any errors. Than I use the SQL utility to test the query: TABLE SCAN (LIVE), Unoptimized Part : F1="1000".

Now I use the Re-Index short-cut from Data Achitect and after that, the showplan in SQL utility ist AOF SCAN (LIVE).

The index-properties look exactly as bevor the reindex-operation.

Former Member
0 Kudos

Is there anybody who can help us?