Stored Procedure Performance

[Joe Pacelli]

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

[John Carlson]

On 1 Jul 2003 09:22:16 -0700, [email]joepacelli@earthlink.net[/email] (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

[Paul Watson]

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 #
[url]www.oninit.com[/url] #

[zz3top]

For those of you using CFMX, SQLServer and stored procedures - has anyone been
able to quantify the performance gain of performing your queries through Stored
Procedures versus the usual CFQuery? I understand the issue with injection
attacks - so we don't need to cover that here today.

My basic understanding tells me that the Stored Procedure saves alittle time
on the database side as the SQL is already parsed and optimized by the database
and passing in a query string via CFQuery probably needs to have that done.
However, the main bulk ot the performance time is more related to the query
itself, table indexes, the data in the tables, yadda yadda...

Am I missing something or is there more to it? Also, your experiences with
moving from an application using all CFQuery to relying on stored procedures
would be helpful.

Thanks

[PaulH]

for the real skinny on this you should probably ask on the sql server list. but
since you asked, *these* days ms sql server's dynamic SQL probably performs
about as well as sp do for your basic CRUD for small recordsets (you can get
dynamic sql data path caching using built in sp to execute the sql). any logic
beyond that or for larger recordsets you should probably be using an sp. as a
simple example, a single sp name would be shot over the LAN to the db server vs
gobs and gobs of dynamic sql.

besides security there are plenty of other valid reasons for using sp. command
and control, RAD, etc.

[Jigar_Patel]

We are using Cold Fusion version 5. For storing client Variables on each page
cold fusion is using inline queries to select, insert and update the client
variable information in Client Management database. This eats up lot of RAM on
SQL Server as the inline queries are not properly parameterized. An example is
(from syscacheobjects on SQL Server 2000)

(@P1 text,@P2 char(53))UPDATE CGLOBAL SET data=@P1,lvisit={ts '2006-05-10
09:12:56'} WHERE cfid =@P2

As the query has not parameterized lvisit, this query is cached for each call
where the lvisit is different. In order to optimize the RAM usage on SQL
Server, i have two options
1) Change this query so that lvisit is parameterized --> Not so good option
but will be better
2) Convert all inline queries in procedures and use stored procedures --> The
best option.

I would like to know, if there is any way i can change these inline queries to
stored procedure with Cold Fusion 5?

Jigar Patel
Manager Database Systems
Ticketsnow.com