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.
Best Answer
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.
And the following statement makes it clear that the (majority of) optimization occurs at compilation.
But there are cases where the DBMS has to do additional optimization at query 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.