Re: Parameterized query ( with bind parameters ) running much slower than non-parameterized
From: sheongperk (anonymous_at_discussions.microsoft.com)
Date: 08/09/04
- Next message: Paul C: "Re: SQL Server and Tomcat"
- Previous message: geossl: "Configure JDBC Connection Pool size"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 9 Aug 2004 02:45:06 -0700
Thank you Joe, that was the answer that I was looking for
for the past 2 months.
Sheong Perk
>-----Original Message-----
>Hi.
>I'll bet your performance returns if you add this to your
connection properties:
>p.put("sendStringParametersAsUnicode", "false");
>
>By default the driver sends string parameters back as
nvarchar values
>so no Java 16-bit characters get truncated. Howver, this
means that the
>values won't be used inindex searches for varchar
columns, so you will
>get table scans. If you know you're always dealing with
varchars, not nvarchars,
>that property will help.
>Joe Weinstein at BEA
>
>gary b wrote:
>
>> Hello
>>
>> When I use a PreparedStatement 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 :)
>
>.
>
- Next message: Paul C: "Re: SQL Server and Tomcat"
- Previous message: geossl: "Configure JDBC Connection Pool size"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
