disadvantages in prepared statement
[199 byte] By [srinivas_420_javaa] at [2007-10-3 4:11:15] 
hii could not tell the answer for the following question in my interview can u pleae tell me the answer for this questionone of the disadvantages with prepared statement in jdbc?
PreparedStatement inherits from Statement, so you would expect PreparedStatement to provide some form of enhanced functionality over statement (and it does). The enhanced functionality might have been a more interesting topic, and perhaps you abbreviated the question, but you might have asked; "Disadvantages of a Prepared Statement compared to what?"
The only time I use a Statement is if the database optmiizer needs more information (the dynamic values available in Statement) to generate an optimal explain plan (how the database goes and gets the data, which indexes to use, etc). I'm guessing use of Statement over PreparedStatement for this reason is required less then 1% of the time, and it hardly seems worthy of an interview question!
WorkForFooda at 2007-7-14 22:11:43 >
The only possible disadvantage in using PreparedStatement (that I can think of) is that it may have a larger memory footprint. That said, I've heard that most db drivers use an implementation of PreparedStatement regarless of whether or not you use Statement.
That said, I haven't used Statement in years and I see absolutely no reason to. I have no idea what WorkForFood is talking about.
SoulTech2012a at 2007-7-14 22:11:43 >
> one of the disadvantages with prepared statement in
> jdbc?
1. It can be slower with low numbers of actual reuses.
2. The driver might not support prepared statements at all. Unlikely but possible.
3. The driver might allow prepared statements but in fact the driver and/or database does not do anything different (although this isn't necessarily a real disadvantage.)
4. The developer (interviewer) doesn't know how to use prepared statements and consequently has had a 'problem' that only exists in their own mind.
jschella at 2007-7-14 22:11:43 >
>I have no idea what WorkForFood is talking about.
Sorry, perhaps an example will help clarify:
Using a PreparedStatement using two replacement parameters:
String sql = "SELECT COL1 FROM MYTABLE WHERE COL1 = ? AND COL2 = ?";
ps.setSetring("WORK");
ps.setString("FORFOOD");
Contrasted against a Statement using literals:
"SELECT COL1 FROM MYTABLE WHERE COL1 = 'WORK' AND COL2 = 'FORFOOD'";
When you execute these two queries, they can generate different explain plans, for example the Statement may use an index while the PreparedStatement may attempt a full table scan. The optimizer is choosing a more efficient explain plan for the Statement because it has more information on which to make a decision (the literal values). A full table scan will be significantly slower then using an index to return the same results. In some databases, you can provide hints, but in some cases, the only way to get this query to use an index is to provide the actual values WORK and FORFOOD in the query itself. When using literals rather then replacement parameters it should be more efficient to use Statement then PreparedStatement (see jschell's list #1). So, in these cases, you should use a Statement rather then a PreparedStatement to get best performance from your query. And again, this issue comes up very infrequently but can be quite debilitating to an applications performance when it does occur.
WorkForFooda at 2007-7-14 22:11:43 >
> > one of the disadvantages with prepared statement
> in
> > jdbc?
>
> 1. It can be slower with low numbers of actual
> reuses.
I seriously doubt it. Even if you did lose a couple milliseconds, who cares.
> 2. The driver might not support prepared statements
> at all. Unlikely but possible.
I agree that we take for granted that the driver will implement things well and that may not be the case. But as you say, unlikely.
> 3. The driver might allow prepared statements but in
> fact the driver and/or database does not do anything
> different (although this isn't necessarily a real
> disadvantage.)
Like what? It's the database that will be caching the query plan (and/or depends on the db).
> 4. The developer (interviewer) doesn't know how to
> use prepared statements and consequently has had a
> 'problem' that only exists in their own mind.
Now you're losing me. :-)
As I said, there are "virtially" no disadvantages.
SoulTech2012a at 2007-7-14 22:11:43 >
Is everyone on board with WorkForFood's point now? Because it's a valid one and if I was asked this question it would be my response.Again in summary query plan cacheing (if it is happening with the implementation) could be expensive with different parameters at execution time.
cotton.ma at 2007-7-14 22:11:43 >
> > > one of the disadvantages with prepared statement in
> > > jdbc?
> >
> > 1. It can be slower with low numbers of actual
> > reuses.
>
> I seriously doubt it. Even if you did lose a couple
> milliseconds, who cares.
There is a difference between can be and will be.
Nothing in my statement addresses whether it is significant either.
>
> > 3. The driver might allow prepared statements but in
> > fact the driver and/or database does not do anything
> > different (although this isn't necessarily a real
> > disadvantage.)
>
> Like what? It's the database that will be caching the
> query plan (and/or depends on the db).
>
No I said it doesn't do anything at all. No caching of anything. Probably because the database doesn't have anything to support 'prepared' statements.
> > 4. The developer (interviewer) doesn't know how to
> > use prepared statements and consequently has had a
> > 'problem' that only exists in their own mind.
>
> Now you're losing me. :-)
Unfortunately that really can be a problem.
jschella at 2007-7-14 22:11:43 >
I note that the OP phrased the question that it will be a disadvantage rather than that it can be.Hopefully that was just mis-phrased by the OP rather than by the interviewer.
jschella at 2007-7-14 22:11:43 >
Statements are probably better when you don't know in advance what your query is. For example an adhoc query tool. i.e. the user can type in various queries. PreparedStatements require you to know in advance what the data types and arguments are.
steve - http://www.jamonapi.com - Track sql performance with the open source jdbc proxy driver
Message was edited by:
ssouza
ssouzaa at 2007-7-14 22:11:43 >
PreparedStatements dont "require" us to know anything unless we want them to. You can use any received String to create a Statement as well as PreparedStatement.
George Reese in his book Database Programming with JDBC and Java clearly mentions that Statements are NEVER worth using even when each query is likely to be different than the previous one.
Having mentioned that, I think the argument of having a large memory footprint (possibly) and managment of query plans when they can change with every query (like in a generic query tool) might me a disadvantage.
Such performance benefits may be accountable if you are using your grandpa's computer ;) but still its good to have an idea of what performs better than the rest and when.
mdoubledragona at 2007-7-14 22:11:43 >
Also, in a web application PreparedStatments due to most queries being executed once per page hit (which is the norm). Sometimes when a query is in a loop it may improve performance, but for the driver I couldn't even detect a measurable difference in this case, but your mileage may vary.
This isn't really an advantage of Statements, but in my opinion people overestimate the benefit of reusing PreparedStatements as a performance improvement. SQL injection is the best reason to use PreparedStatements although there can be ways around that when using Statements too.
One thing I don't like about PreparedStatements is that they wreak hell on code reuse as you must call different set methods which are dependent on the data type of the column, and force you to write a bunch of "setXXX" methods for every new query you write.
I have a few little apps that use Statements as a way to ignore the details of jdbc Connections/Statements/ResultSets and all the opening closing that is required of them.
For example abstracting the whole of jdbc away in the following manner.
// You could remove any potential sql injection in executeQuery(...)
List data = Utils.executeQuery("select * from data where salary>=100000 and age<30");
I am not against PreparedStatements at all, but to say there are no benefits to Statements is too extreme.
ssouzaa at 2007-7-14 22:11:43 >
<<George Reese in his book Database Programming with JDBC and Java clearly mentions that Statements are NEVER worth using even when each query is likely to be different than the previous one.>>
Well how does he back this up? How would he recommend you execute ad hoc queries via a PreparedStatement? Also is his main complaint SQL injection?
ssouzaa at 2007-7-14 22:11:43 >
He writes that the worst that could happen when using PreparedStatement is that you dont get any performance benefit as each query is different from the previous one. But again as I mentioned previously, I dont quite agree with it. The PreparedStatement overhead must be there but too small to consider, or mention as George Reese did.
mdoubledragona at 2007-7-14 22:11:43 >
> He writes that the worst that could happen when using
> PreparedStatement is that you dont get any
> performance benefit as each query is different from
> the previous one.
Unless that was further qualified it is not true.
A prepared statement can take time to set up, that could even include a round trip to the database. And that means it will be slower in certain circumstances.
Those circumstances can be rare.
jschella at 2007-7-14 22:11:43 >
One thing bothering me here is that performance is the least benefit in my opinion of using PreparedStatements.
Preventing SQL injection attacks and proper and portable value formatting are the major reasons.
ssouza complained about different set methods I don't see how Statements are any better. The ONLY time there are better is as mentioned if you are allowing ad-hoc queries.
Other than that one should choose PreparedStatement over Statement 99% of the time. (The remaining 1% the times where the performance is actually an issue)
cotton.ma at 2007-7-21 10:25:20 >
> One thing bothering me here is that performance is
> the least benefit in my opinion of using
> PreparedStatements.
>
> Preventing SQL injection attacks and proper and
> portable value formatting are the major reasons.
>
> ssouza complained about different set methods I don't
> see how Statements are any better. The ONLY time
> there are better is as mentioned if you are allowing
> ad-hoc queries.
>
Certainly.
> Other than that one should choose PreparedStatement
> over Statement 99% of the time. (The remaining 1% the
> times where the performance is actually an issue)
I doubt it is even that often. I have certainly never encountered that need.
jschella at 2007-7-21 10:25:20 >
No one has addressed my comment that you can hide jdbc completely by using Statements.The query reduces to one line of code per my example. I know everyone uses PreparedStatements which is ok as I do, but I do think they have some disadvantages noone every acknowledges.
ssouzaa at 2007-7-21 10:25:20 >
> No one has addressed my comment that you can hide
> jdbc completely by using Statements.The query
> reduces to one line of code per my example. I know
> everyone uses PreparedStatements which is ok as I do,
> but I do think they have some disadvantages noone
> every acknowledges.
It was specifically addressed in reply #10.
Repeating it....Prepared statements do not require you to know what the SQL is in advance.
Expanding further on your original post you will find it likely that it is not sufficient to simply use jdbc for a database GUI tool. For example how do you determine when a statement is complete? JDBC isn't going to do it for you.
jschella at 2007-7-21 10:25:20 >
I dont think that you can avoid the SQL injection when you concat user entered data to SQL statements unless you do a complete validation and escaping on data which will be an overhead in development and DBMS specific.
You can argive that the time to setup the PreoaredStatement can be higher than that of the Statement. But still that will depend on the driver and DBMS.
I have once heard someone here complaining about PreparedStatement taking 2secs to create. In such cases if the prepared statement is not reused enough that will be a problem.
And also if you are looking in a hackers point of view not allowing SQL Injection can be a disadvantage :)
LRMKa at 2007-7-21 10:25:20 >
> No one has addressed my comment that you can hide> jdbc completely by using Statements.The query> reduces to one line of code per my example. I kind of felt it was addressed several times.Okay then. It's bullshit.
cotton.ma at 2007-7-21 10:25:20 >
Database Connectivity New Topic
- how to store and retrieve image in a apache derby using JDBC
- Connection pools
- Images and MS Access
- LOGICAL ORGANIZATION OF APPLICATIONS WEB WITH ARCHITECTURE OF THREE LAYERS
- executeUpdate() returns 1 , but actually it has'nt updated the DB tables
- executeUpdate() returns 1 , but actually it has'nt updated the DB tables
- Best JDO Implementation?
- Pls Help java.sql.SQLException: Unsupported feature: refreshRow
Database Connectivity Hot Topic
Database Connectivity
All Classified
- Archived Forums
- Enterprise & Remote Computing
- Java Essentials
- Desktop
- Core
- Solaris Operating System
- Security
- Database Connectivity
- Java Mobility Forums
- Web & Directory Servers
- Development Tools
- Application & Integration Servers
- Java HotSpot Virtual Machine
- Other Topics
- E-Mail, Calendar, & Collaboration
- Developer Tools
- General
- Administration Tools
- Sun Hardware
- Storage Forums
- Java Enterprise System
- StarOffice
- Open Source Technologies
- BigAdmin
- Real-Time
- Java Coding