What does it mean when I say that a prepared statement is precompiled?

Question

I am using MySQL in Java . I do not have a good understanding of PreparedStatement .
I know that it is better to use PreparedStatement than Statement . The reason is that it is compiled.

What do we mean by compiled?

Answer 1

When you use a prepared statement (for example, a precompiled statement), as soon as the DB receives this statement, it compiles it and caches it so that it can use the last compiled statement to invoke the same statement in sequence. Therefore, it becomes precompiled for consecutive calls.

Usually you use a prepared statement with binding variables, where you specify variables at run time. Now, what happens for the sequential execution of prepared statements, you can specify variables that are different from previous calls. From the point of view of the database, it does not need to make an instruction every time, just insert the binding variables during rum. So it gets faster.

Other benefits of prepared statements are: -

1) SQL injection attack protection

2) Faster for consecutive calls of the same operators

How it works: -

• Precompilation is performed by the database. Some simpler databases do not precompile statements at all. Others may pre-compose it in a call to prepareStatement, and yet others can do this when the call is first called in an instruction, taking into account the parameter values ​​when compiling (creating a plan) the instruction.

• Databases that precompile statements usually cache them, so in all likelihood ps1 will not be compiled again. Some JDBC drivers (such as Oracle) even cached prepared statements, so they didn’t actually close it when ps.close () was called.

• Databases are usually cached by statements until something pushes them out of the cache.

See wiki for more details.

Answer 2

At a high level, there are two steps to an SQL query. Firstly, the request is compiled from a text format into an internal representation of the processing to be performed. Secondly, the processing is performed.

The fact that "precompiled" means that the first step has been completed, so it does not need to be repeated. The compilation phase may require some effort, especially to find optimal optimizations for the execution path (which indexes to use, which join methods, etc.). For simple queries, this can increase overhead. For more complex queries, the overhead is usually much less than trying to actually execute the queries.

As an example of how this can have an effect. Consider a table without indexes. You can create a compiled query that will perform a full table scan. If you add an index later, the compiled query will not be able to use the index because it did not know about it at compile time.

In the MySQL documentation, the compilation phase is mainly described as the optimization phase.

Answer 3

It has already been translated into code that the database server understands. The rest of the variables are saved only for the variables that you insert.

For example, a Java program must also be compiled from source to bytecode, where soruce is human readable for the program, and bytecode is a representation if the source is also understood by the machine