473,756 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

performance implications of calling PREPARE more than once


Hi there,

I'm thinking of using a PREPARE statement inside a stored
procedure, but
am wondering about the performance penalty associated with calling
PREPARE
multiple times. Below is an example of the kind of statements I
was thinking
of using.

...
SET S1 = 'UPDATE MFDP SET reliab=?';
PREPARE PS1 FROM S1;
EXECUTE S1 USING newreliab;
...

Is it true that if PREPARE is called multiple times with the same
statement,
the statement is only compiled once? Does that mean that the
performance
penalty for calling it multiple times would be low (compared with
compiling
it multiple times)?

Thanks for any help,

Bill

Sep 21 '07 #1
2 1760
wi************* *******@gmail.c om wrote:
Hi there,

I'm thinking of using a PREPARE statement inside a stored
procedure, but
am wondering about the performance penalty associated with calling
PREPARE
multiple times. Below is an example of the kind of statements I
was thinking
of using.

...
SET S1 = 'UPDATE MFDP SET reliab=?';
PREPARE PS1 FROM S1;
EXECUTE S1 USING newreliab;
...

Is it true that if PREPARE is called multiple times with the same
statement,
the statement is only compiled once? Does that mean that the
performance
penalty for calling it multiple times would be low (compared with
compiling
it multiple times)?
Depends on your definition of low.
The subsequent PREPAREs will discover that a statement with the same
text and environment (CURRENT SCHEMA, etc..) already exists and reuse
it's compiled plan.
However that discovery, while a fraction of the cost of compilation,
isn't free.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 21 '07 #2


Serge,

Thanks for the info.

Bill
>
Depends on your definition of low.
The subsequent PREPAREs will discover that a statement with the same
text and environment (CURRENT SCHEMA, etc..) already exists and reuse
it's compiled plan.
However that discovery, while a fraction of the cost of compilation,
isn't free.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Sep 22 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
3486
by: Brian Patterson | last post by:
I have noticed in the book of words that hasattr works by calling getattr and raising an exception if no such attribute exists. If I need the value in any case, am I better off using getattr within a try statement myself, or is there some clever implementation enhancement which makes this a bad idea? i.e. should I prefer: if hasattr(self,"datum"): datum=getattr("datum") else: datum=None
10
1742
by: Woody | last post by:
I have a page that is linked into by other pages that pass it query strings, it then reads a config file, displays 1 of several forms, gets posted to itself, depending upon users response may display a different form etc etc. I want to take the config data and have that read by a globals.asa and then use application("VariableName") to reference it. i also want to take the initial query strings and load them into session variables.
0
1414
by: agherring | last post by:
Hi, We've just started evaluating the Data Direct ODBC Oracle driver and comparing performance with Oracle's own driver in a development project we're undertaking. We have struck a problem - functionally the driver's working OK, but we have a performance problem that is associated with the ODBC connectivity layer.
8
2025
by: leo | last post by:
Hello all - I was wondering about the performance implications of explicitly raising exceptions to get information about the current frame. Something like what the inspect module does, with: --- def currentframe(): """Return the frame object for the caller's stack frame.""" try:
7
7165
by: Dima | last post by:
Call to XmlNode.GetElementsByTagName returns XmlNodeList that stays in sync with XmlDocument thanks to events fired by XmlDocument. Once this list is created there is no way to remove its event handlers from the document. Calling GetElementsByTagName second time for the same tag name will create new list and add more event handlers. As result over time these handlers accumulate and reach pretty high number (millions). Every modification...
0
938
by: Mark | last post by:
We're using delegation in our ASP.NET application to connect to a SQL Server that is on the same hub as our IIS 6 web server. What are the performance implications of using Delegation rather than giving rights to the ASP.NET account to connect to our SQL Server? I've read that connection pooling becomes useless if we use delegation since each connection pool is user specific. Assume we'll never have more than 30 simultaneous connections...
2
6509
by: Rajat Katyal | last post by:
Hi: I prepare the statement for execution as follows: PREPARE query(text) as SELECT count(*) FROM transform_customer_billing where inv_no = $1; The problem is Iam not able to execute this prepare statement from the stored procedure defined. I added the statement to call the prepare statement is: EXECUTE query('100023'). The ERROR it prints : Function query(text) doesnot exist. Please suggest the solution for this problem.
2
1450
by: Shawn B. | last post by:
Greetings, Lets say I have this class written in standard C++. I want to expose it to C# programs. So lets say I create a ref class to "wrap" around the standard C++ class. What are the performance implications of doing so? For the sake of clarity, the class is currently written in C#. It is a CPU emulator. C# performs well, but after rewriting it in C++/CLI it performs even better. But I'd like to rewrite it in standard C++ and...
13
4603
by: atlaste | last post by:
Hi, I'm currently developing an application that uses a lot of computational power, disk access and memory caching (to be more exact: an information retrieval platform). In these kind of applications the last thing that remains is bare performance tuning. So for example, you can do an 'if then else' on a bit like a 'case/ switch', an 'if/then/else' and as a multiplication with a static buffer. Or, you can do sorting with an inline...
0
9455
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10031
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9869
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8709
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7242
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6534
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5140
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3805
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3354
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.