sql server programming: I have information that people put in once a month, that I would like
to report on periodically.

I tried doing sums in a function, but it takes a long time. Are there
any recommended ways of storing it periodically, or creating a view?

[quoted text, click to view]

Hi M@,

If you frequently query on aggregated data that changes infrequently,
you can consider using an indexed view. These are described in Books
Online, along with their (many!) limitations.

Since your question is very vague, my answer can't be more specific
thann this.

--
Hugo Kornelis, SQL Server MVP

[quoted text, click to view]

Not enough information to provide tangible help. Functions are very
slow. If you provide the query maybe people can help more.

Jay

Sorry about the short explanation, I was swamped and frustrated on
Friday:)

The query gathers information from a table that is based on several
different factors - some put in forms and submitted, some imported
from excel spreadsheets, and some from DTS packages. They all end up
in a table called ScoreCard.

Now, in order to glean any info from this table, the users need to be
able to see how they are doing(actual) vs some measure. In some
cases, it's a budget, in some cases, it's measured against a constant,
etc. From that, they get scores - zero, half credit, or full credit.
If it were that simple, it'd be a piece of cake.

Each of our buildings(47) has their own numbers and I have to compute
it not only on a monthly basis, but on a YTD basis. ie - look at all
of the months together and compare them against the same rules. I
have stored procedures and functions doing a lot of this based on
Business Rules - measure 1 compares actual vs average monthly.
measure 2 compares that month to zero(if it's over, they get no
points)

Doing all of this is not that hard until I have to start doing all of
the calcs for the month and the YTD. Is there a way for me to
calculate the YTDs and store them somewhere? I don't know anything
about cubes, but could one be used for that?

HEre's the stored proc:

select
[PortfolioID] = Portfolios.EnterpriseID, -- not showing
[PortfolioName] = Portfolios.[Name],
e.EnterpriseID, --not showing
[EnterpriseName] = e.[Name],
EnterpriseLevel.[ObjectiveShortName],
EnterpriseLevel.[ObjectiveDescription],
EnterpriseLevel.[MeasureShortName],
EnterpriseLevel.[MeasureDescription],
EnterpriseLevel.[MeasureBusinessRule], --not showing
EnterpriseLevel.[MeasureMeasure1],
EnterpriseLevel.[MeasureMeasure2],
EnterpriseLevel.[EnterpriseMeasure1],
EnterpriseLevel.[EnterpriseMeasure2],
EnterpriseLevel.[EnterpriseScore],
PortfolioLevel.[PortfolioLevelMeasure1],
PortfolioLevel.[PortfolioLevelMeasure2],
PortfolioLevel.[PortfolioLevelScore],
MeasureLevel.[MeasureLevelMeasure1],
MeasureLevel.[MeasureLevelMeasure2],
MeasureLevel.[MeasureLevelScore],
ObjectiveLevel.[ObjectiveLevelScore] --this is coming out wrong
from Enterprise e inner join
Enterprise_View ev on e.EnterpriseID = ev.EnterpriseID inner join
( -- Select only facilities, leave out companies and portfolios
select EnterpriseID,
[Name]
from Enterprise
where (EnterpriseTypeID = 2) and
(EnterpriseID <> 103) --exclude home healthcare
) Portfolios on Portfolios.EnterpriseID = ev.ParentID inner join
( -- Enterprise Level Information
select e.EnterpriseID,
o.[ObjectiveID],
[ObjectiveShortName] = o.[ShortName],
[ObjectiveDescription] = o.[Description],
[MeasureShortName] = m.[ShortName],
[MeasureDescription] = m.[Description],
[MeasureBusinessRule] = m.[BusinessRule],
[MeasureMeasure1] = m.[Measure1],
[MeasureMeasure2] = m.[Measure2],
m.MeasureID,
m.[PartialScore],
[EnterpriseMeasure1] = sc.[Measure1],
[EnterpriseMeasure2] = sc.[Measure2],
[EnterpriseScore] = dbo.udfn_M1vsM2(sc.Measure1, sc.Measure2,m.
[PartialScore], m.BusinessRule, m.Score)
from ScoreCard sc inner join
Enterprise e on sc.EnterpriseID = e.EnterpriseID inner join
Measure m on sc.MeasureID = m.MeasureID inner join
Objective o on m.ObjectiveID = o.ObjectiveID
where (sc.[Year] = @Year) and
(sc.[Month] = @Month)
) EnterpriseLevel on EnterpriseLevel.EnterpriseID = e.EnterpriseID
inner join

( -- Measure Level Information
select m.MeasureID,
[MeasureLevelMeasure1] = sum(sc.[Measure1]),
[MeasureLevelMeasure2] = sum(sc.[Measure2]),
[MeasureLevelScore] = dbo.udfn_M1vsM2(sum(sc.[Measure1]), sum(sc.
[Measure2]),max(m.[PartialScore]), max(m.BusinessRule), max(m.Score))
from ScoreCard sc inner join
Measure m on sc.MeasureID = m.MeasureID
where (sc.[Year] = @Year) and
(sc.[Month] = @Month)
group by m.MeasureID
) MeasureLevel on MeasureLevel.MeasureID = EnterpriseLevel.MeasureID
inner join
( --Portfolio Level information
select [PortfolioID] = Portfolios.EnterpriseID,
m.MeasureID,
[PortfolioLevelMeasure1] = sum(sc.[Measure1]),
[PortfolioLevelMeasure2] = sum(sc.[Measure2]),
[PortfolioLevelScore] = dbo.udfn_M1vsM2(sum(sc.Measure1),
sum(sc.Measure2),max(m.[PartialScore]), max(m.BusinessRule),
max(m.Score))
from ScoreCard sc inner join
Enterprise e on sc.EnterpriseID = e.EnterpriseID inner join
Enterprise_View ev on e.EnterpriseID = ev.EnterpriseID inner join
( -- Select only facilities, leave out companies and portfolios and
homehealthcare
select EnterpriseID
from Enterprise
where (EnterpriseTypeID = 2) and
(EnterpriseID <> 103) --exclude home healthcare)
) Portfolios on Portfolios.EnterpriseID = ev.ParentID inner join
Measure m on sc.MeasureID = m.MeasureID
where (sc.[Year] = @Year) and
(sc.[Month] = @Month)
group by Portfolios.EnterpriseID,
m.[MeasureID]

) PortfolioLevel on PortfolioLevel.PortfolioID =
Portfolios.EnterpriseID and
PortfolioLevel.MeasureID = EnterpriseLevel.MeasureID inner join
( -- Objective Level information
--score by **objective** == all meaures in that objective added for
all facilities
select m.ObjectiveID,
ObjectiveLevelScore = sum(MeasureTotals.Score)
from Measure m inner join
(
select m.MeasureID,
Score = dbo.udfn_M1vsM2(sum(sc.measure1), sum(sc.measure2),max(m.
[PartialScore]), max(m.businessrule), max(m.score))
from Enterprise e inner join
ScoreCard sc on sc.EnterpriseID = e.EnterpriseID inner join
Measure m on sc.MeasureID = m.MeasureID
where (e.EnterpriseTypeID = 3) and
(e.EnterpriseID <> 103) and--exclude home healthcare
(sc.[Year] = @Year) and
(sc.[Month] = @Month)
group by m.MeasureID
) MeasureTotals on m.MeasureID = MeasureTotals.MeasureID
group by m.ObjectiveID
) ObjectiveLevel on ObjectiveLevel.ObjectiveID =
EnterpriseLevel.ObjectiveID
order by [PortfolioName], EnterpriseLevel.[MeasureShortName],
[EnterpriseName]


On Sep 15, 5:36 am, Hugo Kornelis
[quoted text, click to view]

[quoted text, click to view]

Hi M@,

I don't know anything about cubes either. That would be a question for
the analysis services group (microsoft.public.sqlserver.olap, I guess,
or maybe .datamining or .datawarehouse?) However, I can give you a few
pointers that might help you speed up the T-SQL version.

I'm a bit too busy and your query is a bit too long to fully review
every line, but here are a few things that caught my eye:

(snip)
[quoted text, click to view]

This "Enterprise_View" is a view, I guess. I assume that you are aware
that this name will simply be replaced by the definition, so that the
optimizer will see something such as

[quoted text, click to view]

Since I don't know the view definition, I have no way of knowing whether
this is good or bad in this case.

(snip)
[quoted text, click to view]

Using scallar user-defined functions is great for simplifying long
queries and for reusing common code, but it can kill performance. If
it's possible to express the functionality of the UDF as a single
expression, you might see a great performance gain by simply replacing
the UDF call with an inlined version of the logic - even (no, that
should read "expecially") if that involves joining more tables.

(snip)
[quoted text, click to view]

and

[quoted text, click to view]

and

[quoted text, click to view]


This looks to be a good candidate for an indexed view. You might find
that performance of this query improves if you define a view such as

CREATE MyIndexedView -- Please use a better name!!
WITH SCHEMABINDING
AS
SELECT MeasureID, SUM(Measure1) AS MaxMeasure1, SUM(Measure2) AS
MaxMeasure2
FROM dbo.ScoreCard
GROUP BY MeasureID;

CREATE UNIQUE CLUSTERED INDEX MyViewIndex -- Did I mention the naming
thing?
ON MyIndexedView(MeasureID);

If you run enterprise edition, you might find that the optimzier starts
using the indexed view straight away (you may have to use sc.MeasureID
instead of m.MeasureID in the SELECT and GROUP BY). If you run a
different edition, you will have to rewrite the query to use this view
yourself, *AND* you must specify the WITH NOEXPAND optimizer hint when
including the view in the FROM clause.

I'm sorry I don't have the time to immerse myself fully in your code; I
hope that these tips will help you anyway!

--
Hugo Kornelis, SQL Server MVP