Most DBMS products have "Stored Procedures" which, essentially, are compiled chunks of SQL that you can call by name, e.g.
CREATE PROCEDURE foobar
AS
SELECT *
FROM users
On very large queries (many table joins) there can be a noticeable performance hit while the DBMS figures out the optimal query plans. Stored procedure compile and store this plan after first execution (some pre-compile) so the DBMS can just look it up in the cache.
It also removes the need to do runtime parsing (compiled vs. interpreted languages). There are more nuances to Stored Procedures than I can put here, but here's a link to a more in-depth analysis I wrote:
http://stackoverflow.com/questions/59880/are-stored-procedur...
In general, MySQL has limited query plan cache (last I read, it was one cache per database connection and was wiped out when the client disconnected), so this is definitely a win. However, since every other DBMS out there already has robust query cache, I don't think the Postgres/Oracle/MS SQL etc. folks are losing any sleep.
Supporting stored procedures doesn't mean compiling to native code. E.g. sql server just stores a compiled plan so saves of parsing/optimization. Execution is still an interpretation.
Giants are investing into big memory technology no doubt about it, which is great. That's the beauty of the huge big data market - one size doesn't fit all.
SQL Server supports .NET stored procs, which are compiled code bits. Oracle / Sybase support Java.
I believe some DBMS vendors are working on the interpreted nature of the execution, but basically the query plan determination makes up the bulk of the parsing/execution time. Compiling SQL to something... "native" doesn't provide a whole lot of win in terms of execution latency.
CREATE PROCEDURE foobar AS SELECT * FROM users
On very large queries (many table joins) there can be a noticeable performance hit while the DBMS figures out the optimal query plans. Stored procedure compile and store this plan after first execution (some pre-compile) so the DBMS can just look it up in the cache.
It also removes the need to do runtime parsing (compiled vs. interpreted languages). There are more nuances to Stored Procedures than I can put here, but here's a link to a more in-depth analysis I wrote: http://stackoverflow.com/questions/59880/are-stored-procedur...
In general, MySQL has limited query plan cache (last I read, it was one cache per database connection and was wiped out when the client disconnected), so this is definitely a win. However, since every other DBMS out there already has robust query cache, I don't think the Postgres/Oracle/MS SQL etc. folks are losing any sleep.