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? |
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 |
Yes - namely to reveal way more details...
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.
What is callback, what is dd?
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
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.
What character set / collation are the two databases using?