Is a DBMS (MySQL, SQL Server….) interpreted or compiled?

Question

I mean, in the terms of the SQL queries, Are they compiled or interpreted in a low level?. How it works internally, It is a SQL statement interpreted or compiled?.

Answer 1

It's typically working like this:

    SQL String ---[Optimizer]---> Execution Plan ---[Execution]---> Result

I personally like to see the optimizer (query planner) as something very similar to a compiler. It's transforming the SQL statement into something that is more easily executable. However, it's not nativity executable on the chip. This "compilation" is rather expensive--just like compiling C++ code. That's the part where different execution variants are evaluated; the join order, which index to use, and so on. It's a good practice to avoid this whenever possible by using bind parameters.

The execution plan is then taken up for execution by the database. However, the strategy is already fixed. the execution is just doing it. This part is kind of interpreting the execution plan, not the SQL.

After all, it is, somehow, similar to Java or .NET where the compilation turns the source code into a binary form that can be interpreted more easily. If we ignore JIT for this argument, the execution of a Java program is interpreting this meta-code.

---

I have used this way to explain the benefit of using bind parameters for (Oracle) performance in my free eBook "Use The Index, Luke".