News for hackers. Stuff that matters.
Re:The SQLite Query Planner by thrownaway2424 on Friday August 21, 2015 @11:17AM (#10098054)
I always wondered why databases don't allow the direct specification of the query plan, instead of forcing SQL developers and DBAs to try to guess the right words that produces the right query plan, usually. It seems like it would be a lot easier to express the query plan in, say, a protobuf, rather than parsing a DSL on every query.
Re:The SQLite Query Planner by saosebastiao on Friday August 21, 2015 @01:35PM (#10099015)
I know this is a simplistic and ideological notion, but anybody who can manually plan a query better than the query planner should be using their time to write better query planners, optimizers, and statistics collectors as opposed to manually writing query plans.
Re:The SQLite Query Planner by MSM on Friday August 21, 2015 @11:41AM (#10098209)
Most databases have ways for you to specify the exact plan you want used already. Since you mentioned SQL Server, you can force join order, force index usage, force the type of join used (loop, merge, hash), etc.

There are terrible drawbacks using any of those techniques (data changes, indexes being modified / dropped, etc) which is why, imo, they should very rarely be used. However, 'allowing direct specification of the query plan' is already implemented in that respect.

The better way to address the problem of different parameters causing performance issues is forcing recompiles which will solve the problem 95% of the time.

Re:The SQLite Query Planner by bch on Friday August 21, 2015 @02:25PM (#10099335)
This is what collecting and managing statistics[0][1] is about.

I know Oracle and Postgres implements this -- don't know about SQLServer, MySQL, etc...

[0] http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats....

[1] http://www.postgresql.org/docs/current/static/runtime-config...

edit: found/added info for postgres

Re:The SQLite Query Planner by thrownaway2424 on Friday August 21, 2015 @03:50PM (#10099818)
Sure but I don't want to influence the query plan, I want to dictate it.
Re:The SQLite Query Planner by AaronFriel on Friday August 21, 2015 @11:43AM (#10098231)
I know at least Microsoft's SQL Server allows you to simulate this by fixing a query plan: https://technet.microsoft.com/en-us/library/Cc917694.aspx

Having worked with SQL Server for years as a DBA, I've never had to use it once, but I was never working with databases over, say, 1 TB. So while we did encounter slow query problems, they could usually be resolved by introducing reliable indexes, and the performance cost of this was low.