I don't disagree with writing solid SQL. I would go so far as to say some things (most) need to be in stored procedures that are reviewed by competent people. But, some folks don't think about usage sometimes.
This is one of those things I just don't get about folks setting up their databases. If you have a rather large dataset that keeps building via daily transactions, then its time to recognize you really have some basic distinct scenarios and to plan for them.
The most common is adding or querying data about a single entity. Most application developers really only deal with this scenario since that is what most applications care about and how you get your transactional data. Basic database knowledge gets most people to do this ok with proper primary and secondary keys.
Next up is a simple rule, "if you put a state on an entity, expect someone to need to know all the entities with this state." This is a killer for application developers for some reason. It actually requires some database knowledge to setup correctly to be performant. If the data analysts have problems with those queries, then its to to get the DBA to fix the damn schema and write some stored procedures for the app team.
At some point, you will need to do actual reporting, excuse me, business intelligence. You really should have some process that takes the transactional data and puts it into a form where the queries of data analysts can take place. In the old days that would be something to load up Red Brick or some equivalent. Transactional systems make horrid reporting systems. Running those types of queries on the same database as the transactional system is currently trying to work is just a bad idea.
Of course, if you are buying something like IBM DB2 EEE spreading queries against a room of 40 POWER servers, then ignore the above. IBM will fix it for you.
At its simplest its OLTP vs OLAP. Separate the data entry/transactional side of things from the reporting part. Make it efficient for data analysts do do their jobs.
But sometimes, your line-of-business application does analytics queries. Which means you need your app developers to understand how to do OLAP, and you also need a schema design that can run arbtrary OLAP queries within a few orders of magnitude of OLTP speeds (e.g. <10s.)
This is one of those things I just don't get about folks setting up their databases. If you have a rather large dataset that keeps building via daily transactions, then its time to recognize you really have some basic distinct scenarios and to plan for them.
The most common is adding or querying data about a single entity. Most application developers really only deal with this scenario since that is what most applications care about and how you get your transactional data. Basic database knowledge gets most people to do this ok with proper primary and secondary keys.
Next up is a simple rule, "if you put a state on an entity, expect someone to need to know all the entities with this state." This is a killer for application developers for some reason. It actually requires some database knowledge to setup correctly to be performant. If the data analysts have problems with those queries, then its to to get the DBA to fix the damn schema and write some stored procedures for the app team.
At some point, you will need to do actual reporting, excuse me, business intelligence. You really should have some process that takes the transactional data and puts it into a form where the queries of data analysts can take place. In the old days that would be something to load up Red Brick or some equivalent. Transactional systems make horrid reporting systems. Running those types of queries on the same database as the transactional system is currently trying to work is just a bad idea.
Of course, if you are buying something like IBM DB2 EEE spreading queries against a room of 40 POWER servers, then ignore the above. IBM will fix it for you.