Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
3  
"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." That's not entirely true, depending on your configuration the prepared query is cached in the engine and not prepared every time. Specifically for MySQL see: dev.mysql.com/doc/refman/5.6/en/statement-caching.html –  Yannis Rizos Aug 1 '13 at 10:44
    
@YannisRizos very cool. I didn't know that, thanks. –  Mathew Foscarini Aug 1 '13 at 10:45
    
Same thing for Oracle and probably most "large" database engines. –  Mat Aug 1 '13 at 11:06
    
Wouldn't a compiled query basically become a view? –  Pieter B Aug 1 '13 at 11:06
1  
Sql Server also caches queries. –  Robert Harvey Aug 1 '13 at 16:34
show 4 more comments

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.