473,388 Members | 873 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

parameterized queries running slower than non-parameterized queries

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 :)
Jul 20 '05 #1
1 6035
[posted and mailed, please reply in news]

gary b (bo*******@hotmail.com) writes:
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
...
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.
It would have helped if you had included the CREATE TABLE and CREATE INDEX
statements for your table. Now, I will have to guess that your table has
a non-clustered index on type and that index does not include both of
store_groups_id and groups_name, and also at least one these colunms is
absent from the clustered index.

When it is said that prepared queries are more effecient, this is because
the query plan for these are cached and can be reused. This means that
if you resubmit the query you are saved the compilation phase.

Ad-hoc statements can be cached too, and also auto-parameterized, so they
would be very similar to prepared queries. But auto-parameterization
does not always happen, and this seems to be such a case.

When SQL Server builds a query plan, it looks ar the values in the queries
and also at the statistics saved for the table. Now, if a value is a
constant, it knows that this is the value that applies, and no other.

If the statement is parameterized, SQL Server still looks at the current
parameter value as a hint, but it has to consider the possibility that
next time, the value passed might be different.

For this query it seems that SQL Server has a choices between two
alternatives:

1) Scan the data pages of the table.
2) Use the non-clustered index on type.

For a value of type with few rows, using the index is much faster. However,
say that 40% of the rows has ' ' in type. Using the index to retrieve
these rows would be very expensive - a lot more expensive than scanning
the table, because each data page would be read more than once.

SQL Server tends to be conservative here. I've more often seen it choose
table scan incorrectly than incorrectly seeking a non-clustered index.

Had the index included all columns in the query, this would have changed
the scene completely, since SQL Server would have no reason to access
the data pages. As I mentioned the clustered index also has importance.
This is because the pointer to the data page is actually the value of
the clustered-index key. So the columns of the clustered index is present
in all non-clustered indexes.
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?
The behaviour is cetainly related to the architecture of SQL Server. I
don't have experience of other engines, but I would expect most engines
can give you surprises, although which surprises you get may differ
from engine to engine.
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?


The best in this case may be to create a covering index for this
particular query. You can also use an index hint to force use of
the non-clustered index, but if you actually pass a value with many
occurrances your application might grind to an actual standstill.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

114
by: Maurice LING | last post by:
This may be a dumb thing to ask, but besides the penalty for dynamic typing, is there any other real reasons that Python is slower than Java? maurice
15
by: Philip Mette | last post by:
I am begginner at best so I hope someone that is better can help. I have a stored procedure that updates a view that I wrote using 2 cursors.(Kind of a Inner Loop) I wrote it this way Because I...
6
by: Caveman | last post by:
-- I have a situation where doing -- first example -- 1. Get series of values througha query into a string (@val)like '1,2,3,4': declare @val varchar(4000) select @Val = @val + cast(myval as...
77
by: Peter Olcott | last post by:
http://www.tommti-systems.de/go.html?http://www.tommti-systems.de/main-Dateien/reviews/languages/benchmarks.html The above link shows that C# is 450% slower on something as simple as a nested loop....
2
by: Fiddelm3742 | last post by:
Well, I dont exactly know if there was a change in how Console Application projects are run/compiled from VS2003 to 2005 or what, but I have an application (mostly C) that used to run in about 2...
28
by: Jim Hubbard | last post by:
I've noticed (for quite some time now) that .Net UIs are not as responsive (see Franklin Covey's PlanPlus for Windows XP or Symantec's .Net Norton Antivirus or even the .Net version of Paint done...
7
by: (PeteCresswell) | last post by:
Can anybody point me to an example of creating a running sum via SQL. The situation is the application shows a history of bond trades - both as parent transaction and as allocated among funds. ...
11
by: ThunderMusic | last post by:
Hi, I have a windows service that only loads a CSV file and import it's data using SqlBulkCopy in a newly created Sql Server 2005 table using 25000 rows batches. If I build the service in debug...
22
by: paululvinius | last post by:
Hi! Testing som Linq-expressions and tried to measure performance and compare it to pre-Linq programming. The folloing two methods are functional equal but the non-Linq one is twice as fast....
25
by: news.microsoft.com | last post by:
Hi all, First post here. I'm porting an application I wrote in VB6, over to VB.NET 2005. It could be said I'm really struggling with some (most!) of the syntax of VB.NET 2005, but I'm getting...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.