Performance of View vs. Table
Hi Informixers, in order to facilitate analysis of certain sub-groups in a table containing about 1 million rows, I was asked to create a new table that just contains certain fields of those rows that meet specific criteria, in the hope that joining this new table with other tables will yield much better performance, especially when "group by" etc. is involved. Will it be sufficient to just create a view that meets the definition, or must I really create and populate a new table? Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. med Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+
I would create a view. Using a table might be faster, (I couldn't tell you how much faster without knowing exactly what you're putting in it) but you give up too much in integrity. If the original table changes, you have to add triggers and such to maintain the integrity of the new table. This adds overhead during inserts, deletes and updates. As long as your indexes are designed well, you can still get excellent performance from the view with no overhead during inserts, deletes and updates. In article <3911736E.C528B3CA@ana.med.uni-muenchen.de>, richard.spitz@ana.med.uni-muenchen.de wrote: > Hi Informixers, > > in order to facilitate analysis of certain sub-groups in a table > containing about 1 million rows, I was asked to create a new table > that just contains certain fields of those rows that meet specific > criteria, in the hope that joining this new table with other tables > will yield much better performance, especially when "group by" > etc. is involved. > > Will it be sufficient to just create a view that meets the definition, > or must I really create and populate a new table? > > Regards, Richard > -- > +--------------------------+------------------------------------------+ > | Dr. med Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | > | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | > | Klinikum Grosshadern | FAX : +49-89-7095-6420 | > | 81366 Munich, Germany | GSM : +49-172-8933578 | > +--------------------------+------------------------------------------+ > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.