I've got a stored procedure which is called from a VB application on
the clients machine. This SPL takes about 2-3 seconds to run. I can
run this SPL over and over and recieve 2-3 second results. Then all of
sudden it will go to 40+ seconds and stay this way for 1-2 minutes. It
will then eventually return back to the normal 2-3 seconds.
The client has 2 databases on this instance. One a live and one a
test. The live has no problems and does not see this issue. While
running this on test I've ran onstat and found no exclusive locks, yet
it just sits there. There is no one else hitting this test base other
than myself.

Any ideas of what to look for.

I've put set explain with stored procedure and it revealed nothing.

Thanks,
Joe P
Senior Software Engineer

On 1 Jul 2003 09:22:16 -0700, joepacelli@earthlink.net (Joe Pacelli)
wrote:

What are your checkpoint durations?

>I've got a stored procedure which is called from a VB application on
>the clients machine. This SPL takes about 2-3 seconds to run. I can
>run this SPL over and over and recieve 2-3 second results. Then all of
>sudden it will go to 40+ seconds and stay this way for 1-2 minutes. It
>will then eventually return back to the normal 2-3 seconds.
>The client has 2 databases on this instance. One a live and one a
>test. The live has no problems and does not see this issue. While
>running this on test I've ran onstat and found no exclusive locks, yet
>it just sits there. There is no one else hitting this test base other
>than myself.
>
>Any ideas of what to look for.
>
>I've put set explain with stored procedure and it revealed nothing.
>
>Thanks,
>Joe P
>Senior Software Engineer

Look at checkpoints [Most likely]
Look at the 'set wait mode' and locking issues [you seemed to have
checked]

Set explain is only relevant when the SPL is created.

Joe Pacelli wrote:
>
> I've got a stored procedure which is called from a VB application on
> the clients machine. This SPL takes about 2-3 seconds to run. I can
> run this SPL over and over and recieve 2-3 second results. Then all of
> sudden it will go to 40+ seconds and stay this way for 1-2 minutes. It
> will then eventually return back to the normal 2-3 seconds.
> The client has 2 databases on this instance. One a live and one a
> test. The live has no problems and does not see this issue. While
> running this on test I've ran onstat and found no exclusive locks, yet
> it just sits there. There is no one else hitting this test base other
> than myself.
>
> Any ideas of what to look for.
>
> I've put set explain with stored procedure and it revealed nothing.
>
> Thanks,
> Joe P
> Senior Software Engineer

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #