• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Preparing a PreparedStatement

 
Ranch Hand
Posts: 162
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If I execute two SQL statement, one having a select clause bit different from the other but same where clause, do the preparation happen twice ie., will the database/app consider them as two different SQL statements.
eg)
Statement #1
Select col1 form table1
where col1 = null
Statement #2
Select col1,col2 form table1
where col1 = null

Do the process of preparing the statement and EXPLAIN PLAN vary between oracle and sybase ??
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Vinod,
Yes, those would be prepared separately as they are different SQL strings. Further, the execution plan may or may not be the same. For example, the first query could use an index (if there is one) and not touch the table at all.

Different databases have different ways of generating an execution plan.
 
Vinod John
Ranch Hand
Posts: 162
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jeanne Boyarsky:
Vinod,
For example, the first query could use an index (if there is one) and not touch the table at all.



Here I don't understand something, the index is used mostly for the WHERE clause filter, why should two queries with same WHERE clause execute differently (in terms index usage), or you mean it is up to the DB query optimizer to decide what to do what query ?.

Another question

How different is preparing a statement in the below two code

1) connection.prepareStatement("select * from table where col1=1");

2) stmt = connection.prepareStatement("select * from table where col1=?");
stmt.setInt(1,1);

I know in the earlier version of JDBC, where the compiled prepared statements where cached on a connection, the first one is inefficient because each execution of the prepared statenemt is considered as different one. In the recent version of JDBC this "facility" is no longer there (except when you use some application servers), so does the above optimization still holds.
 
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Vinod John:


Here I don't understand something, the index is used mostly for the WHERE clause filter, why should two queries with same WHERE clause execute differently (in terms index usage), or you mean it is up to the DB query optimizer to decide what to do what query ?.



It depends on the database, but here's what many (even most) db servers will do. Suppose we have a RanchHand table, with an Id column, Name column, Password column, and a bunch more columns. Suppose we have an index on the Name and Password columns (yes, an index can index more than one column).

Now suppose we query
select Password from RanchHand where Name="Joe Smith"
The db can look in the index just described for this name, and then get the password right there -- it doesn't have to go to the table itself to get it because it's already in the index. On the other hand, if additional columns were requested by the query, it would have to access the table.

Thus the most efficient execution of a query can depend on the precise columns to be returned.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Vinod,
Loren explained what I was thinking very well. Now on to your other question. (I assume that you mean to have "stmt =" in front of the first one as well.)

If you only want to call this stmt with col1=1, the two are equivalent. But the second one is more flexible in case you want to call it with different values. If the col1 value is different, you get the effect you described.
 
reply
    Bookmark Topic Watch Topic
  • New Topic