Unicode Oddity from a Unicode People Soft Database

Unicode Oddity

from a Unicode People. Soft Database SELECT FROM WHERE ; emplid, name, LENGTH(name), BLENGTH(name) ps_personal_data emplid = '007’ EMPLID NAME LENGTH(NAME) BLENGTH(NAME) -------------007 Cona¿ová, d 10 12

Insert 10 characters in 11 bytes CREATE TABLE fred (fred 10 VARCHAR 2(11)); Table created. INSERT INTO fred SELECT name FROM ps_personal_data WHERE emplid = '007'; ERROR at line 1: ORA-01401: inserted value too large for column

Insert 10 characters in 12 bytes CREATE TABLE fred (fred 10 VARCHAR 2(12)); Table created. INSERT INTO fred SELECT name FROM ps_personal_data WHERE emplid = '007'; 1 row created.

How does People. Soft create tables? CREATE TABLE fred (fred 10 VARCHAR 2(30) CHECK (LENGTH(fred 10)<=10) ); Table created.

So. . . • Length checking constraint on EVERY character column in the database! • >500, 000 user constraints • What effect does this have on performance

Experiment 1 create table test_nocons (id number , field_01 varchar 2(30) … , field_20 varchar 2(30) ); create table test_cons (id number , field_01 varchar 2(30) CHECK(LENGTH(field_01)<=30 ) … , field_20 varchar 2(30) CHECK(LENGTH(field_01)<=30 ) );

Populate tables, trace enabled alter session set sql_trace = true; BEGIN FOR i IN 1. . 10000 LOOP INSERT INTO test_nocons VALUES (i , RPAD(TO_CHAR(i), 11, '. ') … , RPAD(TO_CHAR(i), 30, '. ') ); COMMIT; END LOOP; END; /

Results of Experiment 1 • Insert 10000 rows • CPU time for recursive SQL • on my 500 Mhz Laptop – No constraints: – With constraints 11. 08 s 13. 23 s

Experiment 2 • Now deliberately generate different SQL statements, forcing parse every time. BEGIN FOR i IN 1. . 1000 LOOP EXECUTE IMMEDIATE 'INSERT INTO test_nocons VALUES ('||i||', RPAD(TO_CHAR('||i||'), 11, ''. ''))'; END LOOP; COMMIT; END; /

Results of Experiment 2 • >99% parse time • Duration of parse CPU – Without Constraints: – With Constraints: 41. 05 s 156. 93 s

Conclusion • Execution of constraints adds overhead. – On my PC 15%-20% increase in CPU consumption. • If you have much SQL parsing this will aggravate the problem. In my case 4 times worse.

Unicode Oddity David Kurtz Go-Faster Consultancy Ltd. david. kurtz@go-faster. co. uk www. go-faster. co. uk