replace function performance

[Former Member]

replace function,on over 64000 bytes length string , takes 35 ms at ver 9.0.2 same procedure same function, less then 64000 bytes, takes 1.2 sec at ver 17

if command out the replace function line, there are no differences.

any suggestions?

[VolkerBarth]

any suggestions?

Yes - namely to reveal way more details...

[Former Member]

sa17 with replace function takes 1.2 sec without replace function takes 47 ms dd length 61000 bytes

sa9 with replace function takes 35 ms dd length 69000 bytes

select callback+'('+'"'+replace(dd,'"','''')+'")'

seems problem is replace function.

[Vlad]

What is callback, what is dd?

[Former Member]

not important callback select * into dd from dba.xx for xml auto; dd is a long varchar variable store xml output like dd is "<menu name="hamburger"...." i need convert double quotes to single quotes but replace function in sa17 more than 4 times slow according to sa9 on same data

[Vlad]

Ok then... Based on your text I have decided to reproduce the issue. Here is my SQL statement that I tested on my 17.0.9.4793: https://pastebin.com/NDBZQNyS

The results are here:

I know some values are close to 1 second, in my old tests I got something equal to 1.4 (using the random string with 69050 bytes).

If you can prepare a sample case to compare with our machines, please do it. Indeed people can review your case here, if they can do the measurements quickly.

[johnsmirnios]

What character set / collation are the two databases using?

[Breck_Carter]

When benchmarking how long SQL takes to run, be careful not to include unrelated overhead costs like how long ISQL takes to process SQL statements.

In this case, 10 UPDATE statements using REPLACE took 250 milliseconds, or 25 milliseconds per REPLACE plus UPDATE.

The benchmark SQL was put inside a BEGIN block to exclude ISQL overhead.

Conclusion: The long string may be expensive to handle, but it may not be the fault of REPLACE... maybe it is the two concatenation "+" operations, both of which require the entire string to be copied.

CREATE TABLE t (
   pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data LONG VARCHAR NOT NULL );
INSERT t ( data ) VALUES ( 
'oSL8qNuMEKAfGO266USE 
 ... Vlad's string ...
AIAgwL7TQhETRXgSShUf' );
COMMIT;
CHECKPOINT;

BEGIN
DECLARE @started TIMESTAMP;
SET @started = CURRENT TIMESTAMP;
UPDATE t SET data = REPLACE ( data, ' ', '[' ) WHERE pkey = 1;
UPDATE t SET data = REPLACE ( data, '[', '/' ) WHERE pkey = 1;
UPDATE t SET data = REPLACE ( data, '/', '*' ) WHERE pkey = 1;
UPDATE t SET data = REPLACE ( data, '*', '&' ) WHERE pkey = 1;
UPDATE t SET data = REPLACE ( data, '&', '^' ) WHERE pkey = 1;
UPDATE t SET data = REPLACE ( data, '^', '!' ) WHERE pkey = 1;
UPDATE t SET data = REPLACE ( data, '!', '+' ) WHERE pkey = 1;
UPDATE t SET data = REPLACE ( data, '+', '=' ) WHERE pkey = 1;
UPDATE t SET data = REPLACE ( data, '=', '~' ) WHERE pkey = 1;
UPDATE t SET data = REPLACE ( data, '~', ' ' ) WHERE pkey = 1;
SELECT DATEDIFF ( MILLISECOND, @started, CURRENT TIMESTAMP ) AS elapsed_msec;
END;

        elapsed_msec 
-------------------- 
                 250