Hi everyone,

Which concept in your opinion is better when it comes to big and complicated queries that require data from many tables? Do you try to keep it highly normalized (2NF?) or denormalize into big blocks of data? Do you use views? I use postgreSQL and did many things to improve performance but select queries are still not fast enough. Most of them require data from seven tables and there is around one thousand of them for each user's request. Does organising data in different way help to improve perfomance? All help will be appreaciated.

Regards,
N.

I go for highly normalized. Third normal form (TNF) is the lowest that I'll voluntarily go in an OLTP database.

Yes, changing the way that data is organized will change system performance. In general, I prefer to keep the individual pieces (rows) of data atomic, meaning that they can't be subdivided in any meaningful way.

Where performance is concerned, I think that every row should have a Primary Key, and every row that needs to reference another row should use a Foreign Key. I always index the Primary Key (usually implicitly via constraints), and almost always index Foreign Keys (again, usually using constraints).

If you are really needing to execute a thousand queries for user requests, then this is fertile ground for a datawarehouse. I wouldn't try to handle that type of processing in an OLTP database.

-PatP

nupek:
As Pat said, normalize at least to 3NF. In OLTP you can gain a lot by normalizing and building the proper indexes.

You can try creating some "materialized" views to help with those thousand queries...

Thank you for the answers, Pat, m42. I am afraid that data warehouse is not an option but my database is normalized to 2NF at least and I use indexes as well as tried to use cluster on index. Hope it will not be a complete disaster in the end. I also decided to use postgresql instead of mysql hoping it will be faster on many cores. Do you think it was wise?

Regards,
Nupek.

If warehousing is not an option, i guess you really shoud give "materialized views" a try.. I don't know if you're familiar with the concept but think of it as a table to hold your frequent query results...