- By looking at a DB schema how do you know how well indexed the DB is?
- How do you know how many rows are in it?
- How do you know about it performance
I have always been of the opinion that you
- Normalise first, create a beautiful piece of art that is a nicely and appropriately normailised database.
- Add indexes.
- Write your sored procs.
- Check the performance on test data.
- Denormalise if nessecary, however if there were issues I would go over everything with a fine tooth comb first to ensure keys, indexes, constraints, triggers are all in place where appropriate, working as intended and not adding unessecary overhead. Then I would start denormalising.
Yes i am a fan of stored procs, especially for anything that actaully is DB intensive. As Gumble also points out (frequently) it adheres to a basic concept we use daily, encapsulation. Normal Objects and Layers/Tiers cant see into the next object/layer/tier, why should this be broken at the data access layer. Using SP's also (IMO) aids in security and data control (well it can, anyone can butcher code) ;). The OO coders should not have to know the underlying data structure, they know what they want, they just need a means to get it. The Dba may be adding a whole bunch of stuff that the OO boys dont need to about, inactive flags, triggers, loggin, other extra columns, how the data is retireved etc etc... also this mean performance is left in the DB world and can be easily tested with out any manged code interfering.
One thing i do prefer is isolation of tests, i dont want to not be able to test individual units. A stored proc is a unit, a BusObj --> Repository --> DA--> db --> DA --> Repository --> BusObj is not a very succinct unit. If i want to test that vertical i still can however i want to be able to break it down too.
Then there is the point of ORM's; I'm all for them, anything that gets rid of boring DA code, sweet! But sometime you have some complex resultsets and DB call that do not suit your run of the mill ORM's. I do believe for basic database and when fast turnover of code is of higher importances, then something like NHibernate (perhaps with AR) can be handy... but i think it needs to be reviewed on a case by case basis, unlike some of my peers who believe NHibernate is the silver for all solutions...
no doubt more will come...