Do prepared statements slow down program conspicuously?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

You might also consider retrieving multiple values per statement:

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

This will be faster than individual queries.