Ask HN: Overhead of SQL for App specific database?

1 pointsnetforay12y ago3 comments

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.

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.

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)?

Consider it as 'we take SQLLite/H2 code, and we optimize it for one specific schema and queries'.

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?

Ask HN: Overhead of SQL for App specific database?

1 pointsnetforay12y ago3 comments

Consider it as 'we take SQLLite/H2 code, and we optimize it for one specific schema and queries'.

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?

3 comments

3 comments · 1 top-level

justintocci12y ago· 2 in thread

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.

Best of luck on your project!

netforayOP12y ago

I got your point. But if we can also use all the indexes, and other stuff, but without SQL and mainly without any dynamic lookup. (I dont need to parse SQL, No need to validate if it is correct or not, no need to dynamically find which table to load etc). Don't you think it will be even 1% of faster than SQL? (ofcourse I wont go that way for 1%)

justintocci12y ago

That would then depend on your server. If nothing really changes your server could plan the first run and re-use that plan each time. I know I can do that with Postgres, your server may be different. To figure out what the actual percentage is postgres has the ability to tell you how long it takes to plan a query so I would say try that. I wouldn't hazard a guess since its going to depend on too many factors and its so easy to just find out from the planner.

j / k navigate · click thread line to collapse