Can SQL queries be compiled into byte code or equivalent to enhance performance?
I know that most database servers support prepared statements, but is that the same thing as compiling? Most applications have to prepare statements on run-time so there is no benefit of pre-compiled byte code. Since it's prepared each time the application runs.
I'm also not referring to stored procedures, but strictly SQL statements you execute to get a result set.
The statements are compiled to templates, not byte code. But I suspect that's a semantic issue for your question. The short answer is yes, a prepared statement is equivalent to compiling.
The Wikipedia article on prepared statements explains a number of items that should help clarify things.
1. Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables. 2. The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it. 3. Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result).
And the following statement makes it clear that the (majority of) optimization occurs at compilation.
The overhead of compiling and optimizing the statement is incurred only once, although the statement is executed multiple times.
But there are cases where the DBMS has to do additional optimization at query time.
Not all optimization can be performed at the time the prepared statement is compiled, for two reasons: the best plan may depend on the specific values of the parameters, and the best plan may change as tables and indexes change over time
The article indicates that DB2, Microsoft SQL Server, MySQL, Oracle, and PostgreSQL support prepared statements. I have used prepared statements with DB2 and can verify they work as explained in the article.
