SQL Anywhere database - stored procedure performance

Author

stored procedure performance

Jon Watson

2005-12-27, 9:23 am

I am working with a rather large database(4 gigs) on which a
certain stored procedure is having performance problems..

The procedure executes quickly for the first few thousand
iterations but then the performance starts to degrade. The
commands within the stored prcoedure that start taking
longer and longer as the it executes more and more are
simple Fetch statments out of Dynamic scroll cursors that
get created by the stored procedure.

The procedure has the form of:
1) create the dynamic scroll cursor that will almost always
have only one row
2) fetch the first(and usually only) record
3) execute some code

steps 1 and 3 do not show performance degradation..it is the
Fetch that starts taking longer and longer..

Any help or insight into this performance problem would be
greatly appreciated..we are using ASA 8.0.2.4339.

Pavel Karady

2005-12-27, 9:23 am

I've seen a matching problem somewhere before... I mean other forum *maybe*
(I am aware of multiple instances of this thread in this forum).

I don't remember how did it end, but I remember they were suggesting the
CREATE STATISTICS command, which will make your SP work fast again, but
again, for the first few thousand iterations only.

I suggest changing the fetching with the dynamic scroll cursor to anything
else - will EXECUTE IMMEDIATE suitable for you? Or a FOR statement can do?
That should eliminate your problem.

Pavel

<Jon Watson> wrote in message news:43b15183.7a5.1681692777@sybase.com...
>I am working with a rather large database(4 gigs) on which a
> certain stored procedure is having performance problems..
>
> The procedure executes quickly for the first few thousand
> iterations but then the performance starts to degrade. The
> commands within the stored prcoedure that start taking
> longer and longer as the it executes more and more are
> simple Fetch statments out of Dynamic scroll cursors that
> get created by the stored procedure.
>
> The procedure has the form of:
> 1) create the dynamic scroll cursor that will almost always
> have only one row
> 2) fetch the first(and usually only) record
> 3) execute some code
>
> steps 1 and 3 do not show performance degradation..it is the
> Fetch that starts taking longer and longer..
>
> Any help or insight into this performance problem would be
> greatly appreciated..we are using ASA 8.0.2.4339.

Jon Watson

2005-12-27, 9:23 am

> I've seen a matching problem somewhere before... I mean
> other forum *maybe* (I am aware of multiple instances of
> this thread in this forum).
>
> I don't remember how did it end, but I remember they were
> suggesting the CREATE STATISTICS command, which will make
> your SP work fast again, but again, for the first few
> thousand iterations only.
>
> I suggest changing the fetching with the dynamic scroll
> cursor to anything else - will EXECUTE IMMEDIATE suitable
> for you? Or a FOR statement can do? That should eliminate
> your problem.
>
> Pavel

I apologize for the multiple-threads..my problem started as
one thing and turned into this so I thought I might get
better responses if I broke up the problem. I'll try
changing the code so it doesn't need a Dynamic scroll
cursor...I'm still curious as to why this is happening
though..

Thanks.

Pavel Karady

2005-12-27, 9:23 am

<Jon Watson> wrote in message news:43b15a80.826.1681692777@sybase.com...
> ..my problem started as one thing and turned into this

This is a common situation :) I've also had problems when creating my big
and powerful stored procedures - in a state when just a small step was
needed to engage the full functionality, when trying to get to the bottom of
it, I've realized that it was an ASA bug ;) The worst thing is that this
happened to me more than once...

I remember someone writing something somewhere (in fact, as you can see, I
don't remember much) about internal ASA diseffectiveness when fetching using
cursors - this, if already had not in the latest ASA versions 'n' EBFs,
disappear in Jasper.

I still think removing the cursor and fetching might solve this. I wish you
luck with it.

Pavel

Jon

2005-12-27, 11:23 am

> <Jon Watson> wrote in message
> started as one thing and turned into this
>
> This is a common situation :) I've also had problems when
> creating my big and powerful stored procedures - in a
> state when just a small step was needed to engage the
> full functionality, when trying to get to the bottom of
> it, I've realized that it was an ASA bug ;) The worst
> thing is that this happened to me more than once...
>
> I remember someone writing something somewhere (in fact,
> as you can see, I don't remember much) about internal ASA
> diseffectiveness when fetching using cursors - this, if
> already had not in the latest ASA versions 'n' EBFs,
> disappear in Jasper.
>
> I still think removing the cursor and fetching might solve
> this. I wish you luck with it.
>
> Pavel

Thanks, Pavel.

Sponsored Links