4 ms·

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 wor

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.

MSM 11y ago
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.
AaronFriel 11y ago
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 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.
saosebastiao 11y ago
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.
bch 11y ago
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.htm#g49431 http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.... [1] http://www.postgresql.org/docs/current/static/runtime-config-query.html http://www.postgresql.org/docs/current/static/runtime-config... edit: found/added info for postgres
- thrownaway2424 11y ago
  Sure but I don't want to influence the query plan, I want to dictate it.

MSM 11y ago

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.

AaronFriel 11y ago

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 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.

saosebastiao 11y ago

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.

bch 11y ago

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.htm#g49431 http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.... [1] http://www.postgresql.org/docs/current/static/runtime-config-query.html http://www.postgresql.org/docs/current/static/runtime-config... edit: found/added info for postgres

thrownaway2424 11y ago
Sure but I don't want to influence the query plan, I want to dictate it.