7

I am writing a software that requires me to prepare statements and set the values, execute the query and get results within a loop. This loop might have over 7,000 cycles. If I use simple statements rather than prepared statements, will the execution speed change greatly?

Here is the pseudo code

  1. Prepare Statements
  2. Get a list from somewhere
  3. Iterate through the list
  4. get the prepared statements and do some db querying and close the new resources like result sets.
  5. populate a map using the result and values from the initial list

Thanks.

mwangi
  • 1,616
  • 1
  • 20
  • 23
  • 4
    Prepared statements are generally *faster* than regular queries if you're repeatedly running the same query. – Amber Aug 28 '10 at 07:59
  • 3
    Performance is nice but the real win with PreparedStatements is the parameter binding can do done via the API rather than string concatenation. This is particularly useful for date types. Also prevents SQL injection attacks. – Mike Q Aug 28 '10 at 08:15
  • @amber, there is a preparation overhead which may take a while to buy back before the prepared statements is faster in wall time. – Thorbjørn Ravn Andersen Aug 28 '10 at 08:39
  • @Thorbjørn, Can you please explain a bit more. I believed, the preparation time for Statement and PreparedStatement be same. – Amit Aug 28 '10 at 08:51
  • Why do you believe that? – user207421 Aug 28 '10 at 08:58
  • There is simply more work to do for the database to prepare a prepared statement (placeholders, internal datastructures to be kept etc) than for a simple statement. Some databases do a LOT of work, hence the time spent may take longer to "pay back" in the optimization. However, simply because of http://xkcd.com/327/ prepared statements are useful. – Thorbjørn Ravn Andersen Aug 28 '10 at 09:30
  • @Thorbjørn Ravn Andersen. First, Hahaa for (http://xkcd.com/327/). You are right. A prepared statement will mean more work for the database server. The database server is already too busy with other transactions, so I might resort to using simple statements. Thanks – mwangi Sep 17 '10 at 08:27
  • 1
    I believe your conclusion is incorrect. – Thorbjørn Ravn Andersen Sep 17 '10 at 12:10
  • 1
    It **is** possible for a prepared statement to run slower than adhoc queries. The reason is simple: the database engine might generate a plan that is optimal for @x = 1 but not so optimal for @x = 2, 3, 4 and so on. Generally speaking however, prepared statements inside tight loops will give you better results. – Salman A Sep 15 '17 at 11:26

4 Answers4

8

Prepared statements are FASTER then non-prepared statements if you repeatedly use the same statement with multiple sets of data. I'm unaware of a situation where this is not true.

Once you've prepared a statement, its sent to the DB server which then only has to accept the data each time you call it -- it doesn't have to reprocess the statement every time you bind new data.

So the simple answer is:

No. They don't.

Erik
  • 20,526
  • 8
  • 45
  • 76
  • 3
    Actually, if you execute a statement only once or twice the setup time of a prepared statement might outweigh the gain during execution:) – extraneon Aug 28 '10 at 08:12
  • I meant in his situation in general. His proposed scenario was "iterating through a list". I was unclear in my response. Edited my original post for clarity. – Erik Aug 28 '10 at 08:28
4

Prepared statement are faster for repetitive tasks.

http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html :

If you want to execute a Statement object many times, it normally reduces execution time to use a PreparedStatement object instead.

Colin Hebert
  • 91,525
  • 15
  • 160
  • 151
4

Just some things which popped up : make sure you do not create the prepared statements in your loops. There is some overhead involved, but pays back for itself after the 3rd query or so. Actually with large parameter list it might even be faster for a single query.

Something which does speed up things considerably is running all your queries in a single (or a couple of large) transactions. If it are large datasets you might to 1000 queries per transaction or something similar. (Of course the semantics of your domain model must allow for this, but in my experience that is almost always the case).

The number of queries you can bunch up in a single transaction is somewhat database dependent so some experimentation and reading maybe required.

Peter Tillemans
  • 34,983
  • 11
  • 83
  • 114
1

You might also consider retrieving multiple values per statement:

SELECT id, value FROM table WHERE id IN (?, ?, ?, ?, ?, ?)

This will be faster than individual queries.

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37