Performance issue with name value pairs [message #395535] |
Wed, 01 April 2009 16:01 |
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 #395606 is a reply to message #395565] |
Thu, 02 April 2009 01:07 |
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.
|
|
|