Tomas Reznicek via DBMonster.com
2005-03-08 06:38:23 UTC
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?
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?
--
Message posted via http://www.dbmonster.com
Message posted via http://www.dbmonster.com