Wiki Home

VFP 9 Rushmore And Codepage


Namespace: VFP


According to Aleksey Tsingauz (Fox Team), if CPCURRENT() and CPDBF() don't match, VFP9 won't use indexes on character expressions for optimization.

Queries are not optimized with Rushmore if the table uses code page different from OS and the index expression is of character data type. Therefore, some statements suffer from huge performance hit as Vfp does full scan over the table.

This bug is new to Vfp9 final (build 9.0.0.2412) while all previous Vfp versions including Vfp9 beta (build 9.0.0.1720) work as expected. Following code snippet demonstrate Vfp behavior over the cursor but the same problem is with stand-alone tables.

CLEAR
CLOSE DATABASE
SET OPTIMIZE ON
SET COLLATE TO "MACHINE" && not needed
SYS(3054, 1) && will display Rushmore optimization status

* Test #1: SQL from table with "right" codepage
? '*** Testing: CPDBF() = CPCURRENT()' + REPLICATE('*', 40)
CREATE CURSOR cpSystem;
 CODEPAGE = CPCURRENT();
 (key i, data c(1))
INSERT INTO cpSystem (key, data) VALUES (1, 'a')
INDEX ON key TAG key
INDEX ON data TAG data
SELECT COUNT(*) AS char_Hits FROM cpSystem WHERE data = 'a' TO SCREEN
SELECT COUNT(*) AS integer_Hits FROM cpSystem WHERE key = 1 TO SCREEN
USE IN 'cpSystem'

*-- Test #2: SQL from table with "legacy" codepage
? '*** Testing: CPDBF() # CPCURRENT()' + REPLICATE('*', 40)
nCp = IIF(CPCURRENT() = 1250, 895, 1250) && any different CP than OS
CREATE CURSOR cpOther;
 CODEPAGE = (m.nCp);
 (key i, data c(1))
INSERT INTO cpOther (key, data) VALUES (1, 'a')
INDEX ON key TAG key
INDEX ON data TAG data
SELECT COUNT(*) AS char_Hits FROM cpOther WHERE data = 'a' TO SCREEN
SELECT COUNT(*) AS integer_Hits FROM cpOther WHERE key = 1 TO SCREEN
USE IN 'cpOther'


Both optimization levels are FULL in the first test. The second test shows NONE for character tag and FULL for integer tag, although both should be FULL.

There is nothing (new) about “codepage” and “code page” which could justify this flaw in on-line help. In the new chapter named Considerations for Creating Index Expressions I found this: Visual FoxPro evaluates index expressions by reading character data from the table without additional code page translation even when current code page (CPCURRENT()) does not match code page tagged to the table (CPDBF()) in order to produce the same index regardless of the current code page. In my opinion, this should not kill Rushmore. I’ve tried SYS(2300, 895, 1) with no help.

We have many tables marked with legacy codepages and frequently get some from our partners. Some tools and applications we use are written in previous versions of Vfp and even in FPD. So we can’t easily transfer all tables to the same codepage. With FoxPro this was easy task for many years, why this was changed in final version of Vfp9?
Let's recap basic facts:

If CPCURRENT() and CPDBF() don't match, Rushmore will be disabled in VFP9 for such table because the indexes may not match the table data after data's converted to the current code page. There is another explanation: The comparison comes made in CPCURRENT() space, where the order is different from CPDBF() space, and therefore the engine cannot use the order of the index (on CPDBF()) for to filter the table. Let's call above mentioned table “legacy table” for brevity.

Following table summarize behavior of different FoxPro versions over legacy table. Table has index on field table.data (INDEX ON data TAG data):
Statement FPD 2.5b Vfp8 SP1 Vfp9 final
SEEK CHR(133) OK OK OK
LOCATE FOR table.data = CHR(133) OK OK slow, but OK
COUNT FOR table.data = CHR(133) OK OK slow, but OK
SELECT *
FROM table
WHERE table.data = CHR(133)
OK OK 1) FAIL 1) 2)
SELECT table.*, table2.data
FROM table, table2
WHERE table.data = CHR(133)
AND table2.data = table.data
2)3)
FAIL 4) OK OK 2)
1) Opens another copy of table even if table is already opened, copy remains opened after the query. And for each query another copy is left open. Even if you have full path in WHERE clause, open dialog appears.
2) Memo fields are not converted to the current codepage. It's considered a bug and hopefuly will be fixed.
3) table2 has CPDBF() = CPCURRENT() and index on data.
4) No records are fetched.

Although both explanations from the thread shed some light on Vfp internals, I don't see a reason for removal of Rushmore support for legacy tables. Sooner or later Vfp must convert data while processing statement to show result in the current codepage. If Vfp is aware of codepage differences and is capable of conversion why it could not apply this “knowledge” to index data? Vfp8 did this. Vfp9 uses the same way to store data to indexes as previous Vfp versions so why to change the way how to read them or use them?

I don't think this is a question of VFP 8 having done this and VFP 9 not doing it. My impression is that VFP 8 and earlier versions could give you wrong results and that VFP 9 was fixed so that you always get the right results.--TamarGranor

I don't know much about code pages, but I have a therory: if 'data' stored in a dbf can be ordered in two different ways depending on code page, then I can see how the index would be usless for doing a query. the index is a buch of pointers - you can't expect the pointers to point somewhere else when the codepage changes. (again, I don't really know what I am talking about, so someone who does should put this in there words. or just delete it.) -- ?cfk

Rushmore suddenly doesn't work on any character field (binary types included) and doesn't work in virtually any statement SCAN (FOR), LOCATE (FOR), … Developers are commended to convert their tables to one codepage as Vfp leave some internal business on them. That is something I don't expect from the new version of my development tool. And it doesn't matter whether I can do this with my database or not (in fact I cannot). If it is impossible for the new internal data engine of Vfp9 to offer Rushmore over legacy table, I'd like to expand SET ENGINEBEHAVIOR 80 so that will force Vfp9 to use Rushmore again.

This has been done for VFP9 SP1. To sum up, you can either change CPDBF() to match CPCURRENT() by changing the DBF codepage, or the codepage that VFP runs under, or use SET ENGINEBEHAVIOR 80 under VFP9 SP1 to enable Rushmore. - Jim Saunders

Default character set used for files is selectable via CODEPAGE=xxx entry in CONFIG.FPW. But in reality only the same codepage as Windows runs on is useful. The catch is Vfp uses system fonts (Arial, Tahoma, Courier …) and they do not support arbitrary codepage. Vfp will translate data from the table to selected codepage, but on the display will be another character (from the Window's codepage).

So basically, after I fix a database from one of my clients and send it back, the application should convert all tables to OS's codepage (dbc too ?) before being used by the application so my client doesn't experience serious performance degradation ? - edyshor
( Topic last updated: 2012.02.21 11:41:04 AM )