wbrianwhite (8/25/2009)
Look at the execution plan, look at and think about what you are typing. Having complex queries will benefit from compiling and recompiling a stored procedure. This is because having an overview of a complex query is best described as impossible. Breaking it down in separate parts and reviewing their individual impact on the complete picture.
No offense, but I disagree. One large query that gets all your data at once is likely to be significantly faster than separate stored procedures that grab bits and pieces. The fundamental nature of a SQL query is that you saying "these are the results I want", and SQL Server then figures out the best way to give them to you. When you tell it 5 different things you don't let it optimize for what you really want.
Depends on what you are optimizing. If you are optimizing dev time, then yes, small building block procs are faster. If you are optimizing run time (like the theoretical example) then it is worth spending more dev time to get a faster running query.
Non taken:-)
However I wasn't talking about breaking up 1 SP into many. The SP's I was talking about consist of a lot of seperate steps of data processing. Analysing these seperately is the only way to go. Writing simple selects should not be a problem if you call yourself a developer. I write the standard selects blindfolded with the 'best' execution plan. It are the complex procedures which cause the problems I talk about.