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?

asked 06 Jan, 13:37

luygur's gravatar image

luygur
112
accept rate: 0%

any suggestions?

Yes - namely to reveal way more details...

(06 Jan, 16:09) Volker Barth

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.

(07 Jan, 03:39) luygur
Replies hidden

What is callback, what is dd?

(07 Jan, 04:40) Vlad

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

(07 Jan, 04:50) luygur

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.

(09 Jan, 04:32) Vlad
2

What character set / collation are the two databases using?

(09 Jan, 12:21) John Smirnios
showing 3 of 6 show all flat view

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 
permanent link

answered 09 Jan, 09:31

Breck%20Carter's gravatar image

Breck Carter
28.6k478640932
accept rate: 21%

edited 09 Jan, 09:32

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×256

question asked: 06 Jan, 13:37

question was seen: 125 times

last updated: 09 Jan, 12:21