Optimization with VFP9 2

[Nro]

Hi all

After I installed VFP9 (from VFP 8) I notice a performance issue for some of my input screen. I'm using a lot of SQL queries and Views, and the system seems very slow with VFP9.

I did some testing with SQL select and it gives me weirds results.

The command ;

SYS(3054,1)

SELECT custMain.Mn_code ;
FROM custMain ;
WHERE custMain.Mn_Nom1 = "irving" ;
INTO TABLE pipo1

give me ‘Rushmore optimization level for table custMain : partial’ in VFP 9.0

If I do the same in VFP 8.0, I have full optimization level (I have and index on Mn_Nom1 and on DELETED(), SET DELETE is on).

Now, if I do this query ;

SYS(3054,1)

SELECT custMain.Mn_code ;
FROM custMain ;
WHERE custMain.Mn_code = 123 ;
INTO TABLE pipo1

it gives me full optimization level in both VFP 8 and VFP 9. The field ‘custMain.Mn_code’ is Integer and ‘custMain.Mn_Nom1’ is string.

Maybe I forget one SET() in VFP9. I’ve read the VFP doc, but zipo.

Thanks in advance.

Nro

 

[bborissov]

check SET ANSI

Borislav Borissov

 

[Nro]

Hi Borislav

I checked SET("ANSI") and it's OFF by default. No change if I issue SET ANSI ON, I have the same result. I look in the doc and they talk about padding the shorter expression with spaces. I've try to pad with spaces the shorter expression by hand (PADR("ir1572", FSIZE("Mn_Number"))), but I've receive the same optimization level (partial)

Thanks

Nro

 

[bborissov]

Check current code page and code page for the table. I think they also can affect the optimization.

Borislav Borissov

 

[TamarGranor]

In VFP, if the current code page (CPCURRENT()) is different than the code page used to create the table, indexes for character fields can't be used for optimization. This is to prevent errors.

Tamar

 

[Nro]

I change the codepage of the table and now it works !

I think I hate code pages. A lot of my tables are coming from old DOS systems, with all these accentuates letters (è é ê …). The code page for almost of them is 437 (ms-dos) but the general codepage is 1252. If I use cpZero to change the codepage, I lost my accentuate characters. I’ll have to use SQL Select to recreate all my tables, reindex everything, re-test… Argg.

Anyway, thanks Borislav and Tamar, a star for you.

BTW, is it a new behavior in VFP 9 ?

Nro

 

[TamarGranor]

Yes, the requirement for CPCURRENT() to match CPDBF() is new in VFP 9. It fixes a bug which can give bad results in earlier versions.

Tamar