To De-Normalize or Normalize ? 
Author Message
 To De-Normalize or Normalize ?

How do you get faster response-times from queries ?

Some people argue that you

A)
have to De-Normalize your database, so that you have fewer joins, which in turn
results in faster response-times from your queries.

B)
have to Normalize your database, so that you can have more data in memory, which
in turn results in faster response-times from your queries.

What is YOUR experience ?

Why and/or When do YOU either De-Normalize or Normalize your database ?

TIA - Christian

*-*-* Disclaimer *-*-*
The Questions, Answers, Opinions etc. stated in this
message are my own !



Mon, 12 Mar 2001 03:00:00 GMT
 To De-Normalize or Normalize ?

Both are right. You have to ask for more questions.
No general answer.
If the answer was so simple, no need for DBA.
Continue, it's a long quest !

.

Quote:
>How do you get faster response-times from queries ?

>Some people argue that you

>A)
>have to De-Normalize your database, so that you have fewer joins, which in
turn
>results in faster response-times from your queries.

>B)
>have to Normalize your database, so that you can have more data in memory,
which
>in turn results in faster response-times from your queries.

>What is YOUR experience ?

>Why and/or When do YOU either De-Normalize or Normalize your database ?

>TIA - Christian

>*-*-* Disclaimer *-*-*
>The Questions, Answers, Opinions etc. stated in this
>message are my own !



Mon, 12 Mar 2001 03:00:00 GMT
 To De-Normalize or Normalize ?
Depending upon exactly where your response time is lacking, there are two other
possible solutions that immediately come to mind:
    - Appropriate additional indexes for frequently hit column(s)
    - Precalculated summary tables

Quote:

> How do you get faster response-times from queries ?

> Some people argue that you

> A)
> have to De-Normalize your database, so that you have fewer joins, which in turn
> results in faster response-times from your queries.

> B)
> have to Normalize your database, so that you can have more data in memory, which
> in turn results in faster response-times from your queries.

> What is YOUR experience ?

> Why and/or When do YOU either De-Normalize or Normalize your database ?

> TIA - Christian

> *-*-* Disclaimer *-*-*
> The Questions, Answers, Opinions etc. stated in this
> message are my own !

--

Barry
Speaking only for myself, to do otherwise would be presumptuous.



Mon, 12 Mar 2001 03:00:00 GMT
 To De-Normalize or Normalize ?
One trick I've picked up which sort of fits your post is that when writing
client-server (for me this is forms4.5 on V7.x) I don't denormalise the data
as such but I do soemtimes denormalise the foreign keys.

i.e.  Table TAB1 has column EMP_ID which is a foreign key on to EMP.

Rather than use TAB1 as a base table for a block in a form create a view
which has the join done to denormalise it giving the emplyee details as part
of the view.

In doing this, the join is still performed at query time but is performed on
the server rather than the work being done on the client (in a POST-QUERY
trigger) easing network traffic.  You can then also query on the employee
details without the need for PRE-QUERY work then as well.

Don't know if this helps.

Generally though I'd certainly consider denormalising data if the join to
fetch it is heavy duty and performed often. i.e if the join from TAB1 to EMP
above to get the employee name was through three or four intermediate
tables.

Basically though, you have to take each instance of denormalisation on it;s
own merits.
--
Alan D. Mills

Quote:

>How do you get faster response-times from queries ?

>Some people argue that you

>A)
>have to De-Normalize your database, so that you have fewer joins, which in
turn
>results in faster response-times from your queries.

>B)
>have to Normalize your database, so that you can have more data in memory,
which
>in turn results in faster response-times from your queries.

>What is YOUR experience ?

>Why and/or When do YOU either De-Normalize or Normalize your database ?

>TIA - Christian

>*-*-* Disclaimer *-*-*
>The Questions, Answers, Opinions etc. stated in this
>message are my own !



Mon, 12 Mar 2001 03:00:00 GMT
 To De-Normalize or Normalize ?
Well, de-normalized databases are used in datawarehouse and normalize db
in your application. But the rules can be broken in the 2 above
sceranios
Francois


Quote:

>How do you get faster response-times from queries ?

>Some people argue that you

>A)
>have to De-Normalize your database, so that you have fewer joins, which
in turn
>results in faster response-times from your queries.

>B)
>have to Normalize your database, so that you can have more data in
memory, which
>in turn results in faster response-times from your queries.

>What is YOUR experience ?

>Why and/or When do YOU either De-Normalize or Normalize your database ?

>TIA - Christian

>*-*-* Disclaimer *-*-*
>The Questions, Answers, Opinions etc. stated in this
>message are my own !



Mon, 12 Mar 2001 03:00:00 GMT
 To De-Normalize or Normalize ?
Christian,

Denormalization should be one of the last arguments to get faster
response times from queries.

Why?

Because a clean data model is very important for building your
applications, maintaining the database's integrity and for future
updates.

There are a lot of other possibilities you can check before
denormalize the model. Such as:

1. Correct datamodel
2. Correct indexes
3. Tuned SQL-statements
4. Index on another tablespace than your data.
5. Optimal configuration of drives, tablespaces and tables
6. Correct storage parameters for your tables (fragmentation!)
7. Enough system memory
8. A SQA that is large enough
9. etc, etc, etc

Quote:
>Why and/or When do YOU either De-Normalize or Normalize your database ?

First of all, always start with a normalized datamodel. Only
denormalize if all other possibilities fail. And denormalization is
not the only possibility. There's also duplication, vertical and
horizontal fragmentation, clustering and bitmapped indexes for
example.

Good luck!
-------------------------------------------------------------------------------
--- Martin Jesterhoudt                      

-------------------------------------------------------------------------------



Wed, 14 Mar 2001 03:00:00 GMT
 To De-Normalize or Normalize ?

Quote:

>How do you get faster response-times from queries ?

>Some people argue that you

>A)
>have to De-Normalize your database, so that you have fewer joins, which in
turn
>results in faster response-times from your queries.

>B)
>have to Normalize your database, so that you can have more data in memory,
which
>in turn results in faster response-times from your queries.

>What is YOUR experience ?

>Why and/or When do YOU either De-Normalize or Normalize your database ?

>TIA - Christian

Don't these decisions depend on what you are doing? Example: a colleague of
mine did some OLTP performance work a few years ago. One organisation that
he worked-with (a large international airline) denormalised tables so much
that everything ended-up in one table! (about 100 million rows). Further
info on his work at:

http://www.soi.city.ac.uk/~akmal/html.dir/citybm.html

His papers will be put on-line in the near future, but the OLTP source-code
is currently available from the above page if you want to have a look at the
transactions, indexes, etc.

Regards,

akmal

--
email: akmal(at)bigfoot(dot)com
http://www.bigfoot.com/~akmal/



Tue, 20 Mar 2001 03:00:00 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. To De-Normalize or Normalize ?

2. Normalizing a de-normalized table

3. Normalizing vs de-normalizing

4. Normalize a De-Normalized table

5. Normalizing vs de-normalizing

6. Normalizing vs de-normalizing

7. VIEWs: Showing normalized data as denormalized, distinct rows

8. De-normalizing?

9. Building a Data Warehouse - Normalize or Denormalize?

10. Normalize or denormalize

11. Normalizing vs denormalizing

12. De-normalizing databases


 
Powered by phpBB® Forum Software