Are statements ready to slow down the program noticeably?

Question

I am writing software that requires me to prepare statements and set values, execute a query, and get results in a loop. This cycle can have more than 7000 cycles. If I use simple statements rather than ready-made statements, will the execution speed change significantly?

Here is the pseudo code

• Prepare reports
• Get a list from somewhere
• List iterations
• get prepared instructions and execute some db queries and close new resources, such as result sets.
• fill in the map using the result and the values ​​from the source list

Thanks.

Answer 1

Prepared statements are PHASES and then unprepared statements if you reuse the same statement with multiple datasets. I do not know a situation when this is not so.

After you have prepared the statement, it was sent to the database server, which then must receive data every time you call it - it should not process the instruction every time you connect new data.

So the simple answer is:

Not. They do not do this.

Answer 2

A trained operator repeats faster.

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

If you want to execute the Statement object multiple times, it usually shortens the execution time for using the PreparedStatement object.

Answer 3

Just some of the things that came up: make sure you don't create prepared statements in your loops. There is some overhead, but it pays for itself after the third request or so. In fact, with a large list of parameters, it can be even faster for a single request.

Something that significantly speeds up the work, fulfills all your requests in one (or several large) transactions. If these are large data sets, you can get 1000 transaction requests or something like that. (Of course, the semantics of your domain model should take this into account, but in my experience this is almost always the case).

The number of queries that you can bind in a single transaction depends on the database, so some experimentation and reading may be required.

Answer 4

You can also consider getting multiple values ​​for each statement:

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

This will be faster than individual queries.