SQL Anywhere database - The speed of SET statement (incrementive way)

Author

The speed of SET statement (incrementive way)

Pavel Karady

2005-12-09, 1:23 pm

Hello Sybasists,

I was searching for the cause of slow procedure performance. The procedure is
based on incrementing a LONG VARCHAR variable - it's creating a complicated
file, that will be saved in the end. I've tried various searching techniques -
separating expensive parts, adding info messages, studying plans - until I've
come to the bottom of the SET statement.

I've created a very short and very simple performance test (please don't let
your mind emit negative waves just by looking at it's size, it's really short
and SIMPLE):

BEGIN
DECLARE phile LONG VARCHAR;
DECLARE @philesize INTEGER;
DECLARE @i INTEGER;
DECLARE @j INTEGER;
DECLARE sttime TIME;
DECLARE entime TIME;

// the settings part
SET @philesize = 4000;

SET phile = 'ssadfklmasslkdfnmkj
sadnmfkjasndfaskdljn
f lkjasndfkljnsdakjf
nalskjdnf kjasdnf lkjasndlfkj nalskdjnflk asjndfkasjdn fklsjandf kjasndfklja
nsdklfjansd f';

SET @i = 0;
WHILE @i<@philesize LOOP
SET phile = phile + 'ssadfklmasslkdfnmkj
sadnmfkjasndfaskdljn
f
lkjasndfkljnsdakjf nalskjdnf kjasdnf lkjasndlfkj nalskdjnflk asjndfkasjdn
fklsjandf kjasndfklja nsdklfjansd f';
SET @i = @i + 1;
END LOOP;

// measurements part
SET sttime=CURRENT TIME;

SET @j = 0;
WHILE @j<100 LOOP
SET phile = phile + 'Additional line';
SET @j = @j + 1;
END LOOP;

SET entime=CURRENT TIME;

MESSAGE 'Phile size: ' || LENGTH(phile) || ' Time: ' || DATEDIFF(millisecond
,
sttime, entime) TO CLIENT;
END

The procedure will add a few (100) lines to the 'phile' variable after
incrementing it to an user-defined size and measures the time it takes to add
these 100 lines.

Here's the main part of my post: the speed of SET statement (used in the
incrementive way, aka "SET something = something + ...") heavily depends on
the current 'something', or in our example, current 'phile' size!

Try to play with @philesize setting in the beginning of the shown code and you
will see what I am waving about. My results: @philesize 1000 = time 31 ms;
@philesize 5000 = time 156 ms, @philesize 35000 = time 1110 ms; so we can see
that the 'slowing ratio' is almost a perfect constant (tested on ASA 7.0.4,
"works" on 9.0.2.3221 also)... Switching the LONG VARCHAR to LONG BINARY
doesn't help (I think it's really in the SET statement).

This really hurts, because if there has not been such behavior, our proc would
take about 10 minutes of running time instead of 2 hours 55 minutes!!

The techniques of avoiding this? Many:
1. instead of incrementing variable, inserting rows into a special temporary
table
2. dividing the setting into smaller 'collecting' parts
3. switching to other DB system
....to name just a few.

Please someone explain the database server SET statement principles, so I can
fully understand, why this is so. Are there any corrections prepared (in
Jasper, ...)?

Thanks,
Pavel

Pavel Karady

2005-12-09, 1:23 pm

For everybody's information, after replacing "SET variable = variable + ..."
with "INSERT INTO temptable VALUES (...", the function produces the same
result, just the performance time dropped from 1 hour 48 minutes to 17
seconds. How much faster is it now in %?

Breck Carter [TeamSybase]

2005-12-09, 1:23 pm

So you're saying it takes 0.01 second to append a string to the end of
a 5M string? (35000 copies of 150 characters to build up 5M in phile,
then 100 passes through the timing loop takes 1110ms). I am not
surprised, because SQL Anywhere is a multi-user database engine so it
may not be optimized for one user wanting masses of RAM... but I am
just guessing.

Does SET phile = STRING ( phile, 'Additional line' ) work any faster?
I don't see any reason why it should, but you never know.

If the end result is a file, the UNLOAD SELECT operation might be more
efficient than repeatedly moving virtually the same giant string
around in memory hundreds or thousands of times. It's the technique I
use to build HTML files in SQL, rather than manipulating string
variables: put the small subsstrings into a sortable temporary table,
then use UNLOAD SELECT STRING ( ... ) to build the file at the end.
That's what Foxhound does to build the HTML, and it runs entirely as
SQL: http://www.risingroad.com/foxhound_beta_3.html

Breck

On 9 Dec 2005 09:37:03 -0800, pavel. karady_ns@ns_kogerds
.com (Pavel
Karady) wrote:

>Hello Sybasists,
>
>I was searching for the cause of slow procedure performance. The procedure is
>based on incrementing a LONG VARCHAR variable - it's creating a complicated
>file, that will be saved in the end. I've tried various searching techniques -
>separating expensive parts, adding info messages, studying plans - until I've
>come to the bottom of the SET statement.
>
>I've created a very short and very simple performance test (please don't let
>your mind emit negative waves just by looking at it's size, it's really short
>and SIMPLE):
>
>BEGIN
> DECLARE phile LONG VARCHAR;
> DECLARE @philesize INTEGER;
> DECLARE @i INTEGER;
> DECLARE @j INTEGER;
> DECLARE sttime TIME;
> DECLARE entime TIME;
>
> // the settings part
> SET @philesize = 4000;
>
> SET phile = 'ssadfklmasslkdfnmkj
sadnmfkjasndfaskdljn
f lkjasndfkljnsdakjf
>nalskjdnf kjasdnf lkjasndlfkj nalskdjnflk asjndfkasjdn fklsjandf kjasndfklja
>nsdklfjansd f';
>
> SET @i = 0;
> WHILE @i<@philesize LOOP
> SET phile = phile + 'ssadfklmasslkdfnmkj
sadnmfkjasndfaskdljn
f
>lkjasndfkljnsdakjf nalskjdnf kjasdnf lkjasndlfkj nalskdjnflk asjndfkasjdn
>fklsjandf kjasndfklja nsdklfjansd f';
> SET @i = @i + 1;
> END LOOP;
>
> // measurements part
> SET sttime=CURRENT TIME;
>
> SET @j = 0;
> WHILE @j<100 LOOP
> SET phile = phile + 'Additional line';
> SET @j = @j + 1;
> END LOOP;
>
> SET entime=CURRENT TIME;
>
> MESSAGE 'Phile size: ' || LENGTH(phile) || ' Time: ' || DATEDIFF(millisecond
,
>sttime, entime) TO CLIENT;
>END
>
>The procedure will add a few (100) lines to the 'phile' variable after
>incrementing it to an user-defined size and measures the time it takes to add
>these 100 lines.
>
>Here's the main part of my post: the speed of SET statement (used in the
>incrementive way, aka "SET something = something + ...") heavily depends on
>the current 'something', or in our example, current 'phile' size!
>
>Try to play with @philesize setting in the beginning of the shown code and you
>will see what I am waving about. My results: @philesize 1000 = time 31 ms;
>@philesize 5000 = time 156 ms, @philesize 35000 = time 1110 ms; so we can see
>that the 'slowing ratio' is almost a perfect constant (tested on ASA 7.0.4,
>"works" on 9.0.2.3221 also)... Switching the LONG VARCHAR to LONG BINARY
>doesn't help (I think it's really in the SET statement).
>
>This really hurts, because if there has not been such behavior, our proc would
>take about 10 minutes of running time instead of 2 hours 55 minutes!!
>
>The techniques of avoiding this? Many:
>1. instead of incrementing variable, inserting rows into a special temporary
>table
>2. dividing the setting into smaller 'collecting' parts
>3. switching to other DB system
>...to name just a few.
>
>Please someone explain the database server SET statement principles, so I can
>fully understand, why this is so. Are there any corrections prepared (in
>Jasper, ...)?
>
>Thanks,
>Pavel

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Breck Carter [TeamSybase]

2005-12-09, 1:23 pm

I do remember once using a SET loop to confirm that a long varchar
could hold 2G, and the process seemed to take forever.

On 9 Dec 2005 10:39:21 -0800, pavel. karady_ns@ns_kogerds
.com (Pavel
Karady) wrote:

>For everybody's information, after replacing "SET variable = variable + ..."
>with "INSERT INTO temptable VALUES (...", the function produces the same
>result, just the performance time dropped from 1 hour 48 minutes to 17
>seconds. How much faster is it now in %?

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Pavel Karady

2005-12-09, 1:23 pm

>Does SET phile = STRING ( phile, 'Additional line' ) work any faster?
>I don't see any reason why it should, but you never know.

Time increased to 1141.

>If the end result is a file, the UNLOAD SELECT operation ...

Now that's what I call a starshine. I've been looking for saving files on
local drive from within a procedure for a long time... my very thanks! :))

I'll respond more on Monday.

Pavel

Pavel Karady

2005-12-13, 3:23 am

OK, so we can think of this case as 'solved'. I have to replace SETs with
INSERTs in my procedures made before also, but this is a work I'll love to do
- the fastering effect will pay it.

I personally do not think that 5 MB or 50 MB are masses of RAM, but from the
view of computer (moving it around in the memory slot) it might be so, so I'll
leave this branch as is; the UNLOAD SELECT STRING you've mentioned is a far
better solution that SELECT and then OUTPUT - it's much faster.

Thank you again.

Pavel

John Smirnios

2005-12-13, 8:24 pm

Unless there is some very special case in the code (and I don't think
there is), the set statement needs to be evaluated by computing the
right-hand side, assigning the value to the left-hand side then freeing
the old value. That means that if you start with a string of length
35000 and then append 100 bytes to it, the server needs to allocate
35100 bytes, copy 35100 bytes to it, then free the old 35000. Next
iteration, we need to allocate 35200 bytes, copy 35200 bytes to it, then
free the old 35100 bytes. This should help you to see why the
performance of the concatenation operator will depend on the length of
the string that you are appending to. In fact, it should generally
behave linearly w.r.t. the string length and that's exactly what you are
seeing. Note, however, that once you start needing to swap pages out to
disk (ie your cache is fairly full), you will see a more significant
performance drop.

In theory, a special case could be added by checking that we are
concatenating an expression which has no references to the destination
string. It requires a special case because we still need to be able to
handle cases such as "set str = str + str". In cases such as this one,
we can't start modifying str until the right-hand side is completely
evaluated.

And, fwiw, the problem should disappear in Jasper due to some other
string-related changes that have been made.

-john.
--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Pavel Karady wrote:
> OK, so we can think of this case as 'solved'. I have to replace SETs with
> INSERTs in my procedures made before also, but this is a work I'll love to do
> - the fastering effect will pay it.
>
> I personally do not think that 5 MB or 50 MB are masses of RAM, but from the
> view of computer (moving it around in the memory slot) it might be so, so I'll
> leave this branch as is; the UNLOAD SELECT STRING you've mentioned is a far
> better solution that SELECT and then OUTPUT - it's much faster.
>
> Thank you again.
>
> Pavel
>

Breck Carter [TeamSybase]

2005-12-13, 8:24 pm

On 13 Dec 2005 12:22:14 -0800, John Smirnios
< smirnios_at_sybase_d
ot_com> wrote:

>the problem should disappear in Jasper due to some other
>string-related changes that have been made.

Excellent news!

I know that some folks have the opinion "it's a *database* engine, why
are you running masses of code inside it?"...

....well, that's the way of the future. If it wasn't, you wouldn't have
given us web services and the embedded http engine.

Anyway, string handling is a big deal. I just wish UNLOAD could do it
TO a string variable... no, not the filespec-from-a-string-variable,
but result-set-to-a-string-variable.

Breck The Boringly Repetetive :)

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links