When denormalizing is essential

[Brian McGee]

I have a database with over 450 tables, that until a short while ago
has been strictly 3NF. In cases where complicated permutations of
data are required quickly for certain parts of the system, sometimes
it is not possible to write a query fast enough, due to the complexity
of deriving many nested levels of data.

Therefore for the few instances where this has been needed, I have
created carefully named summary tables, having one-to-one
relationships to real tables (same primary key), and containing only
the values which must be computed.

This works fine. The question now is when to compute them.

Much as I strongly dislike triggers, I have created triggers for the
tables in question, which themselves recalculate the summary values,
and populate the summary tables.

Good news, loading is much faster.
Bad news, now that I'm taking the hit on the save, the saves are far
too slow.

The only way I can see that my saves could be sped up, is to perhaps
spread the work a bit, such that maybe if I am adding fifty records to
a table, that the trigger is somehow disabled before the rows are
added, and re-enabled afterwards, to calculate the lot as a set-based
operation (rather than calling the trigger 50 times), but I don't see
a way to do that.

Any help of any nature on this matter would be greatly appreciated

Cheers

Brian McGee

[Steven Wilmot]

"Brian McGee" <br*********@Sentrio.com> wrote in message
news:83**************************@posting.google.c om...

I have a database with over 450 tables, that until a short while ago
has been strictly 3NF. In cases where complicated permutations of
data are required quickly for certain parts of the system, sometimes
it is not possible to write a query fast enough, due to the complexity
of deriving many nested levels of data.

Therefore for the few instances where this has been needed, I have
created carefully named summary tables, having one-to-one
relationships to real tables (same primary key), and containing only
the values which must be computed.

This works fine. The question now is when to compute them.

Instead of creating these as tables, have you considered creating these as
views (or even inline-functions in certain cases)
Much as I strongly dislike triggers, I have created triggers for the
tables in question, which themselves recalculate the summary values,
and populate the summary tables.

Good news, loading is much faster.
Bad news, now that I'm taking the hit on the save, the saves are far
too slow.

The only way I can see that my saves could be sped up, is to perhaps
spread the work a bit, such that maybe if I am adding fifty records to
a table, that the trigger is somehow disabled before the rows are
added, and re-enabled afterwards, to calculate the lot as a set-based
operation (rather than calling the trigger 50 times), but I don't see
a way to do that.

Any help of any nature on this matter would be greatly appreciated

How are your triggers act

[Steven Wilmot]

Oops - I shall try and complete my email this time, and add a capital 'S',
rather than "(S)end message"

"Steven Wilmot" <st*********@wilmot.me.uk> wrote in message
news:40***********************@news.aaisp.net.uk.. .

"Brian McGee" <br*********@Sentrio.com> wrote in message
news:83**************************@posting.google.c om...

I have a database with over 450 tables, that until a short while ago
has been strictly 3NF. In cases where complicated permutations of
data are required quickly for certain parts of the system, sometimes
it is not possible to write a query fast enough, due to the complexity
of deriving many nested levels of data.
Ah - misunderstanding of "not possible to write the the query quickly" and
not possible to get the query to execute quickly-enough ... Oops
Therefore for the few instances where this has been needed, I have
created carefully named summary tables, having one-to-one
relationships to real tables (same primary key), and containing only
the values which must be computed.

This works fine. The question now is when to compute them.

Instead of creating these as tables, have you considered creating these as
views (or even inline-functions in certain cases)

Ignore my comment above - I mis-read your first post

Much as I strongly dislike triggers, I have created triggers for the
tables in question, which themselves recalculate the summary values,
and populate the summary tables.

Good news, loading is much faster.
Bad news, now that I'm taking the hit on the save, the saves are far
too slow.

The only way I can see that my saves could be sped up, is to perhaps
spread the work a bit, such that maybe if I am adding fifty records to
a table, that the trigger is somehow disabled before the rows are
added, and re-enabled afterwards, to calculate the lot as a set-based
operation (rather than calling the trigger 50 times), but I don't see
a way to do that.

Any help of any nature on this matter would be greatly appreciated

How are your triggers act

How are the triggers written ? Are they designed to work against the entire
inserted/deleted virtual tables, or do they cursor through each row ...
Worse still, do they only work on a single row .

How much of a performance hit have you noticed on the inserts ? There's a
possibility that simplifying the trigger's operation may make things easier.

Also, how important is it that your summary information is up-to-the minute.

One optiouns is a simple trigger that puts all changed records in a
"queue-table", and then have a job that processes anything in this
queue-table once a minute.

Hopefully a few thoughts for you to consider.

Steven

[Erland Sommarskog]

Brian McGee (br*********@Sentrio.com) writes:

Therefore for the few instances where this has been needed, I have
created carefully named summary tables, having one-to-one
relationships to real tables (same primary key), and containing only
the values which must be computed.

Steven Wilmot first suggested views and then retracted it, when he
realized your scope.

But did you consider *indexed* views? That is, views that are materialized,
and then SQL Server will maintain them. Of course this comes with an
overhead for updates, but not as big as for triggers.

On the other hand, far from everything can be implemented as indexed views,
so this may not be a solution. But it's worth investigating.

More details are in the CREATE INDEX topic in Books Online.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Brian McGee]

> How are the triggers written ? Are they designed to work against the
entire

inserted/deleted virtual tables, or do they cursor through each row ... Worse still, do they only work on a single row .
They only work on the row being inserted or deleted. Can you do
otherwise??
How much of a performance hit have you noticed on the inserts ? There's a possibility that simplifying the trigger's operation may make things easier.

I have tried, but the calculations are extremely complicated. They
are running significantly faster than they did when first written, but
have reached their peak and are still too slow!

Also, how important is it that your summary information is up-to-the minute.
Unfortunately it's critical!
But did you consider *indexed* views? That is, views that are materialized, and then SQL Server will maintain them. Of course this comes with an
overhead for updates, but not as big as for triggers.

When are they materialized? I've had more than one scenario with
SQL Server where the documentation says one thing and testing says
another, when it comes to when computed values are materialized!

Brian
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hugo Kornelis]

On 05 Apr 2004 09:07:29 GMT, Brian McGee wrote:

How are the triggers written ? Are they designed to work against theentire

inserted/deleted virtual tables, or do they cursor through each row

..

Worse still, do they only work on a single row .

They only work on the row being inserted or deleted. Can you do
otherwise??

Yes. Any good designed trigger will work on all records in the
inserted and/or deleted pseudo-tables at once.

I've seen triggers that read one row from inserted/deleted into some
variables and proicess those. They will not work as expected as soon
as you insert. update or delete more than one row in an operation.

I've seen triggers that loop through all rows in inserted/deleted,
either through a cursor or through some nifty SQL logic. They will do
what you expect them to, but they're slow.

A good trigger uses the set-oriented approach towards the inserted and
deleted tables. That's what SQL Server is optimised for. If you can
give us some samples of your current trigger code, we might be able to
improve on them.

I have tried, but the calculations are extremely complicated. They
are running significantly faster than they did when first written, but
have reached their peak and are still too slow!

Again: post some samples. You'd be astonished what the combined brain
power of the regular posters here might come up with.

Also, how important is it that your summary information is up-to-the

minute.
Unfortunately it's critical!

In that case, your own suggestion to temporarily disable the trigger
and do all calculations after enough inserts have been processed is
not the way to go.

But did you consider *indexed* views? That is, views that are

materialized,

and then SQL Server will maintain them. Of course this comes with an
overhead for updates, but not as big as for triggers.

When are they materialized? I've had more than one scenario with
SQL Server where the documentation says one thing and testing says
another, when it comes to when computed values are materialized!

If you define a view WITH SCHEMABINDING and then create a UNIQUE
CLUSTERED INDEX on that view, the view will be materialized. Meaning
that the select statement in the view declaration will be executed and
the result set stored, just as if you'd made a new table; after that,
SQL Server will see to it that changes to the underlying tables will
propagate to the view. This increases the execution time for changes
to the base tables but decreases the time to query the view. Plus, the
extra index might sometimes be used to get better execution plans.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Brian McGee]

Thanks!

I've tried Indexed Views on a small-scale test and they seem to work a
treat. I've a bit of remixing to do before the application as a whole
will use them, but I'm confident now that they will work.

Much appreciated :-)

Brian
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Brian McGee]

Grrr.. Ok. Now that I've tried using indexed views for what I actually
need them for, I find that sum, min, max, outer joins, subqueries, etc
are not allowed. What's the bloody use of indexed views if you can't
write complex queries? So I'm back to square one :(

Anyone any ideas?

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Brian McGee]

Grrr.. Ok. Now that I've tried using indexed views for what I actually
need them for, I find that sum, min, max, outer joins, subqueries, etc
are not allowed. What's the bloody use of indexed views if you can't
write complex queries? So I'm back to square one :(

Anyone any ideas?

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hugo Kornelis]

On 13 Apr 2004 09:57:15 GMT, Brian McGee wrote:

Grrr.. Ok. Now that I've tried using indexed views for what I actually
need them for, I find that sum, min, max, outer joins, subqueries, etc
are not allowed. What's the bloody use of indexed views if you can't
write complex queries? So I'm back to square one :(

Anyone any ideas?

Brian,

Too bad that didn't work out.

As Steven and I already suggested before in this thread, we can only
take a stab at optimizing your code if we get to see it. The best way
to get help in this newsgroup is to post the following:

1. DDL for all tables that participate in the problem. You can leave
out the columns that are irrelevant to the situation. Do include all
indexes and all constraints you have.

2. (If necessary to reproduce a problem) Sample data that shows the
problem. Post this in the form of INSERT statements, so that we can
copy and paste it into Query Analyzer and execute it there. Most of us
don't have the time to manually enter a whole set of data. Use the
minimum amount of data necessary to reproduce the problem.
(Note - there are scripts that can generate this kind of output for
you. I think I saw a reference to it not too long ago in
microsoft.public.sqlserver.programming. Google for it if you are
interested.)
(Note to Brian - since you don't ask about strange behaviour but about
performance, you can skip this step, though it might help put across
what you're trying to accomplish)

3. The output you expect.

Make sure to test if executing the DDL and inserts from staps 1 and 2
work as expected in an empty database. Then, copy it in your message
and post.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On 13 Apr 2004 09:57:15 GMT, Brian McGee wrote:

Grrr.. Ok. Now that I've tried using indexed views for what I actually
need them for, I find that sum, min, max, outer joins, subqueries, etc
are not allowed. What's the bloody use of indexed views if you can't
write complex queries? So I'm back to square one :(

Anyone any ideas?

Brian,

Too bad that didn't work out.

As Steven and I already suggested before in this thread, we can only
take a stab at optimizing your code if we get to see it. The best way
to get help in this newsgroup is to post the following:

1. DDL for all tables that participate in the problem. You can leave
out the columns that are irrelevant to the situation. Do include all
indexes and all constraints you have.

2. (If necessary to reproduce a problem) Sample data that shows the
problem. Post this in the form of INSERT statements, so that we can
copy and paste it into Query Analyzer and execute it there. Most of us
don't have the time to manually enter a whole set of data. Use the
minimum amount of data necessary to reproduce the problem.
(Note - there are scripts that can generate this kind of output for
you. I think I saw a reference to it not too long ago in
microsoft.public.sqlserver.programming. Google for it if you are
interested.)
(Note to Brian - since you don't ask about strange behaviour but about
performance, you can skip this step, though it might help put across
what you're trying to accomplish)

3. The output you expect.

Make sure to test if executing the DDL and inserts from staps 1 and 2
work as expected in an empty database. Then, copy it in your message
and post.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)