Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

prepared statement

 
anagha desai
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have read that Prepared statement is more efficient than using plain Statement .

Question 1 >
Why is it so ?
Think it is related to the statement being precompiled
So Is it correct to infer that Prepared statement is precompiled while a simple statement is not ?

Question 2 >
If Prepared statement is indeed precompiled - than where is it precompiled ?
On the Application server or Database server ?

Question 3 >
Who tells / decides that the query needs to be precompiled / stored ?

Question 4 >
Who decides that the prepared statement should be precompiled and stored while a simple statement should not be stored ?

Please help me in understanding these concepts - since all I hear people saying is that "Prepeared statements give better performance since they are precompiled "

Havent understood what happens in the background .

Thanks ,
-anagha
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Question 1 >
So Is it correct to infer that Prepared statement is precompiled while a simple statement is not ?

All statements get compiled into a useful format in the database, PreparedStatements are compiled in such a way that they can be reused (due to the parameters) and other statements can use the precompiled version rather than building it again.

Question 2 >
If Prepared statement is indeed precompiled - than where is it precompiled ?
On the Application server or Database server ?

Typically but not always this is supported by database. Sometimes it is faked by the Driver, or so I have been lead to believe.

Question 3 >
Who tells / decides that the query needs to be precompiled / stored ?

The database just does it, although there is typically a configurable number of statements that get remembered before it starts throwing some away.

Question 4 >
Who decides that the prepared statement should be precompiled and stored while a simple statement should not be stored ?

They both get compiled and stored, although the parameterised version is more likely to be useful for reuse.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want to be a smarty-pants the next time they say that 'PreparedStatements are more efficient', there is an alternate example.

One primary benefit of a PreparedStatement is that the parameters allow the compiled (execution plan? I forget the database term) to be reused for other queries, saving time the next time same query is executed.

However, due to the inclusion of the parameters, the compiled version is not super-optimised since there is some data that it cannot know beforehand. Therefore a query with no parameters will be more efficient in its execution on the database than the parameterised version.

Although: This may be true, but I have never tried it can't think of a case where it would be worth the effort over simply using a PS all the time.

Note also: PreparedStatements are often matched to the precompiled database plan based on the exact, case sensitive query, so always make sure you se the same String where possible.
 
Muhammad Saifuddin
Ranch Hand
Posts: 1324
Android Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Question 1 Answer : Yes

Question 2 Answer : Yes and precompiled statement store in Database Server.

Question 3 Answer : I think PreparedStatement Object (not sure).

here is a [http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html]link[/url] for PreaparedStatement.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic