Home » SQL & PL/SQL » SQL & PL/SQL » Performance issue with name value pairs
Performance issue with name value pairs [message #395535] Wed, 01 April 2009 16:01 Go to next message
patibandlakoshal
Messages: 1
Registered: April 2009
Junior Member
We have 12 tables with 70 columns in each table in the following format.
All of them have got a common column called id. We have a view which joins
all these 12 tables,having approximately 800+ columns. There were performance
issues while querying the view, so we did column profiling found that for 700+
columns in these 12 tables have less than 1% of data (For most of rows the data
in these columns is null) .
So we tried to test the performance by
have two table instead of 12 tables.
1) Table TR which contains 100 columns where data present is >1%.
2) Table TC Contains 3 Columns Id, Column_name,value
-- (Basically name value pairs)

After creating those two tables and loading the data.
we created a view pre_vw which is select id,max(case
when column_name='T0C1'
THEN VALUE END) AS T0C1,
max(case
when column_name='T0C2'
THEN VALUE END) AS T0C2..... T1270
FROM TC GROUP BY ID

This view was performing very bady. Because it has to do max/case evaluation
for almost 700 columns. Is there any other better alternatives?


TABLE_NAME COLUMN_NAME
T0 T0C1
T0 T0C2
T0 T0C3
T0 T0C4
T0 T0C5
..
T0 T0C69

T1 T1C1
T1 T1C2
T1 T1C3
T1 T1C4
T1 T1C5
..
T1 T1C69
.. ..
.. ..
T11 T11C1
T11 T11C2
T11 T11C3
T11 T11C4
T11 T11C5
T11 T11C6
Re: Performance issue with name value pairs [message #395538 is a reply to message #395535] Wed, 01 April 2009 17:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there any other better alternatives?
Why not have a single table with 800+ columns?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Wed, 01 April 2009 17:01]

Report message to a moderator

Re: Performance issue with name value pairs [message #395564 is a reply to message #395538] Wed, 01 April 2009 19:58 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
There's been tons of discussion about "generic tables" and I'm sure you have some opinion after encountering this issue... See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:74676260920442

Some people are able to get generic entity modeling to work for specific cases ==> http://en.wikipedia.org/wiki/Kalido

Have you looked into collecting column level histograms?
Re: Performance issue with name value pairs [message #395565 is a reply to message #395535] Wed, 01 April 2009 20:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Name, value pair data tables are to database performance as pigs are to flying.
While beauty may be in the eye of the beholder, realized performance can actually be quantified & measured.

While name, value pair table are simple & flexible; they just do NOT scale by any measure of real world performance.

Re: Performance issue with name value pairs [message #395606 is a reply to message #395565] Thu, 02 April 2009 01:07 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
BlackSwan wrote on Thu, 02 April 2009 03:09
Name, value pair data tables are to database performance as pigs are to flying.
While beauty may be in the eye of the beholder, realized performance can actually be quantified & measured.

While name, value pair table are simple & flexible; they just do NOT scale by any measure of real world performance.



Very well put.
And if I may add a point:
it is a nightmare for maintenance. Go find if there is an odd columnvalue when a bug is reported.
Previous Topic: Doubt in SQL query
Next Topic: cursor with dynamic table
Goto Forum:
  


Current Time: Mon Nov 04 04:45:20 CST 2024