Hello

When I use a PreparedStatement (in jdbc) with the following query:

SELECT  store_groups_id
FROM store_groups
WHERE store_groups_id IS NOT NULL  
AND type = ?
ORDER BY group_name

It takes a significantly longer time to run (the time it takes for
executeQuery() to return ) than if I use

SELECT  store_groups_id
FROM store_groups
WHERE store_groups_id IS NOT NULL  
AND type = 'M'
ORDER BY group_name

After tracing the problem down, it appears that this is not precisely
a java issue, but rather has to do with the underlying cost of running
parameterized queries.

When I open up MS Enterprise Manager and type the same query in - it
also takes far longer for the parameterized query to run when I use
the version of the query with bind (?) parameters.

This only happens when the table in question is large - I am seeing
this behaviour for a table with > 1,000,000 records. It doesn't make
sense to me why a parameterized query would run SLOWER than a
completely ad-hoc query when it is supposed to be more efficient.

Furthermore, if one were to say that the reason for this behaviour is
that the query is first getting compliled and then the parameters are
getting sent over - thus resulting in a longer percieved execution
time - I would respond that if this were the case then A) it shouldn't
be any different if it were run against a large or small table B) this
performance hit should only be experienced the first time that the
query is run C) the performance hit should only be 2x the time for the
non-parameterized query takes to run - the difference in response time
is more like 4-10 times the time it takes for the non parameterized
version to run!!!

Is this a sql-server specific problem or something that would pertain
to other databases as well? I there something about the coorect use of
bind parameters that I overall don't understand?

If I can provide some hints in Java then this would be great..
otherwise, do I need to turn/off certain settings on the database
itself?

If nothing else works, I will have to either find or write a wrapper
around the Statement object that acts like a prepared statement but in
reality sends regular Statement objects to the JDBC driver. I would
then put some inteligence in the database layer for deciding whether
to use this special -hack- object or a regular prepared statement
depending on the expected overhead. (Obviously this logic would only
be written in once place.. etc.. IoC.. ) HOWEVER, I would desperately
want to avoid doing this.

Please help :)