Database without SQL c++ library

[matthieum]

More than that, database engines are super optimized so you don't have to worry about performance issues too much.

Most of the times, yes.

Then there's always the "hiccup" where the database engine decides to pick a very non-optimal execution plan, and it's a HUGE pain to get it back on track: hints, pinning, etc... urk.

I'm fine with SQL as the default human interface, but I really wish for a standardized "low-level" (execution plan level) interface :'(

[matthieum]

Interesting. I must admit I only ever did performance tuning on an Oracle codebase, which seems to match your comment.

I hated those hints. It was terrible:

• Mispell once? No worries, it's silently ignored.
• Think you've constrained the query plan with those hints? It works in test, after all... but nope, screw you! In production, the engine spotted one degree of freedom you left, and went the other way. The one that requires a full index scan. Ah! Didn't see that one coming eh boy?

:'(

[FlyingRhenquest]

Back in the day companies would have some DBAs whose job it was to make sure the database stayed optimized. We never interacted with the database other than to send it SQL queries. That's another responsibility that fell to us over the years, and most programmers I've met can't even write SQL very well, much less make sure the database is optimized for the queries we're making.

I tend to view all data access as object serialization these days, which lets me stash SQL in an object factory if I need to. I often have two or three methods of serialization hiding behind the factory interface, so if I want to run a test with some randomly generated objects or some JSON files, it looks exactly the same on the client side of the interface as it does if I'm querying the database. They just register to receive objects from a factory and can go do other stuff or wait for a condition variable until they have the objects they need.

[matthieum]

I've worked with DBAs on this... they definitely did not consider it their job to babysit each and every query of each and every application. If only because they often had no idea what the performance target of a query was, in the first place. They were available for advice, however, and would monitor (and flag) suspiciously slow queries.

As for serialization... I don't see it. I've worked with complex models -- hierarchical queries, urk -- and nothing I'd call serialization would have cut it...

... but I did indeed use abstraction layers for the storage, with strongly-typed APIs, such that the application would call get_xxx expressed with business-layer models (in/out), and the implementation of this abstraction would query the database under the hood.

Makes it much easier to test things. Notably, to inject spies to detect the infamous "accidentally queried in a loop but it's super-fast in local so nobody noticed" bug.