We have built a tool to generate code for Web Applications(Server Side Code + Client Side). We generate all SQL related to that specific application. Users provide specification using some highlevel interface designed specifically for taking such input.<p>Because we generate all the code(Java) in theory it is possible for us to generate database code(Instead of SQL) that is very optimized for that application. I am not a big expert in designing database internals, but I know a bit on how do they do the indexing, query optimization etc.<p>The question is: Consider an application like BaseCamp(or some CRM) where structure of the database is fixed, and the quarries performed on the database is all known in advance(including info on how often we perform them), How much optimization can we do by eliminating SQL and directly interacting with the internals of database(that too optimized for our specific schema)?<p>Consider it as 'we take SQLLite/H2 code, and we optimize it for one specific schema and queries'.<p>I know this is always specific to the schema and the queries, but I want to get an idea, so that I can decide to go in that direction or not. Can it be 10x to 100x faster in general?
I may not be understanding your question so I'll re-phrase it before I answer it. You're asking how much faster you can expect to get an answer to a query if you bypass the SQL engine of the database itself. The answer would be a negative number every time. Any decent database will be highly optimized. You show me code that runs faster than an engine and in two minutes I could tune the query, reconfigure you table structure or add an index and beat your bypass code no problem. Any speed increase in your bypass code is going to come from some assumption about your data that you simply aren't communicating to your database engine.<p>Best of luck on your project!