5 ms·

It's not so much existing at the fringe of performance, but there have been weeks of my life lost to needing to tune a query or an index. Sure, some of that tim

It's not so much existing at the fringe of performance, but there have been weeks of my life lost to needing to tune a query or an index. Sure, some of that time is actually building the index I just added. But I don't want to have to figure out a query plan. If I'm building a simple CRUD app, then I don't need the complexity that SQL provides. If I'm building something bigger, then I probably need to think about the query plans. I would have loved a tool that I could hand a proposed schema, estimates of the data, and some SQL I want to run that would come back and tell me "That's stupid, because you're doing a join on computed data; add this index, denormalize this field, and remove this clause from your query." but I'm also realistic enough to know that such a tool isn't plausible.

traviscj 10y ago
Dunno, sounds easier than chess.
kls 10y ago
MSSQL has one: https://technet.microsoft.com/en-us/library/ms176005(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms176005(v=sql.1... Postgres has explain and some third parties make query tuners. Oracle has a host of third party tunes, Solar Winds being the first that comes to mind. I don't know if any of these can do it off a proposed schema but you could create a dummy database and your insert/read routines run a script against it to simulate transactions and run the capture on these tools and they will explain the recommended adjustments. Some of them like MMSQL query tuner will export a script that can make the adjustments. The biggest thing for DB performance for most CRUD applications is to ensure you eliminate table spools on joins. If you find all of those you are ahead of the game in most instances.
- shaftway 10y ago
  The worst of my time was using Oracle, around 2008. Back then SolarWinds was just a consulting company. And I don't really do much relational work anymore. A lot of my stuff ends up being documents with simple keys organized in hierarchical ways, and the few relational things I do are easily handled with that model, and wouldn't justify an actual database. Why should I have to know what a table spool is and why they're bad?

traviscj 10y ago

Dunno, sounds easier than chess.

kls 10y ago

MSSQL has one: https://technet.microsoft.com/en-us/library/ms176005(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms176005(v=sql.1... Postgres has explain and some third parties make query tuners. Oracle has a host of third party tunes, Solar Winds being the first that comes to mind. I don't know if any of these can do it off a proposed schema but you could create a dummy database and your insert/read routines run a script against it to simulate transactions and run the capture on these tools and they will explain the recommended adjustments. Some of them like MMSQL query tuner will export a script that can make the adjustments. The biggest thing for DB performance for most CRUD applications is to ensure you eliminate table spools on joins. If you find all of those you are ahead of the game in most instances.

shaftway 10y ago
The worst of my time was using Oracle, around 2008. Back then SolarWinds was just a consulting company. And I don't really do much relational work anymore. A lot of my stuff ends up being documents with simple keys organized in hierarchical ways, and the few relational things I do are easily handled with that model, and wouldn't justify an actual database. Why should I have to know what a table spool is and why they're bad?