Preparing a PreparedStatement

[Vinod John]

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 ??

[Jeanne Boyarsky]

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]

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.

[Loren Rosen]

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]

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.