I think this points out a fundamental development approach for which I'd like the opinions of some of the more experienced guys out there . . .
Looking at this simple query, in my early days I would probably have written it like this:
I no longer do so.
select avg(salary) avgSalary
Here's something that I would currently do:
Excuse me for how I explain this, as my knowledge of transactions and locking is very weak, but from my actual experience I have noticed that the first methodology holds a transaction during the entire double scan of the Staff table while the second methodology breaks-up that transaction into two different transactions, allowing others to access the Staff table in-between the two scans. This may not seem like much, with as trivial a table as this, but I have found that when you are dealing with complex queries against large tables, it is best to break-up the query into smaller queries that can be executed individually, sequentially, and more quickly.
declare @avgSalary decimal(9,2)=
This is from a guy who has never learned to use SQL Profiler, but it seems to me logical that the second methodology will lead to a generally improved system performance. At least I, anecdotally, have found that to be the case.
Am I dreaming, or am I thinking in the correct direction?
Last edited by PracticalProgram; 11-19-11 at 17:32.
Maverick Software Design
(847) 864-3600 x2