March 2, 2011 at 3:35 am
Ive been given the task of generating a data display based on various totals for various products across multiple offices.
Ive created a stored procedure that basically generates a series of totals (counts) for specific criteria. The database is highly normalised so there are lots of joins between the tables used to generate the totals.
So I basically have a stored procedure with multiple sql sections performing counts
eg
@var1 = select Count(id) where blah, blah joins, etc
@var2 = select Count(id) where blah, blah joins, etc
@var2 = select Count(id) where blah, blah joins, etc
are there any 'best practice' rules I should be aware of in this scenario or is there a better way to achieve what im after ? Obviously performance is an issue and I have to generate these totals in real time to display on screen
March 2, 2011 at 7:12 am
One thing so consider is whether or not to run a bunch a separate single count queries, as you have proposed, or to run a single query with a number if separate aggregates.
as an example:
select sum(case when blah = this then 1 else 0 end) as cnt1,
sum(case when blahblah = that then 1 else o end) as cnt2
from TABLE
[where blah blah]
If any of the individual queries is doing a table scan then you may very well be better off doing all of your calculations in a single scan. Only way to tell is to examine the query plans and ultimately tray it different ways.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply