Forum Stats

  • 3,782,585 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

Share PreparedStatement across connections

2687995
2687995 Member Posts: 1

PreparedStatement compiles text SQL query into some internal format. For sure compilation takes some time and some resources. As far as PreparedStatement belongs to particular connection software recreates and recompiles PreperadStatement for each connection again and again. How to avoid this drawback? Are there any way to share already compiled PreparedStatement across multiple SQL connections into multithreading application?

Tagged:

Answers

  • Some DBMSes will internally share the compiled query plan for a given SQL. The usual form is via stored procedures,

    so the further compilation to make a prepared/callable statement is minimal, just parsing the procedure name

    and hooking in the parameters. But any of the sharing you want will be done at the DBMS end. You will not

    find a driver that will allow you to share statements across different connections. Multithreading in JDBC is

    to be avoided. Each thread should have it's own connection and subobjects.

  • Gaff
    Gaff Member Posts: 1,490

    Is preparing a statement something that has a high cost?  I would think the far more important thing to reuse would be the execution plan, and as long as you are using the same query text and properly using bind variables than Oracle should be able to do that no matter what the connection.

  • As far as PreparedStatement belongs to particular connection software recreates and recompiles PreperadStatement for each connection again and again.

    Huh? What does 'recreates and recompiles PreparedStatement for each connection again and again' mean?

    A statement is compiled ONCE for a connection; not again and again.

    What is your use case that has the same statement being used by multiple connections?

This discussion has been closed.