Performance
* virtual pivot table vs self-join view
Queries were 5-10 times slower using the self-join view.
* sample queries :
* select id,attribute_001,attribute_002 from pivot_table
where attribute_001 < 200 order by id;
* select * from pivot_table where attribute_001=339 and attribute_002 > 0;
* select id,attribute_001,attribute_002 from pivot_table
where (attribute_001 between 100 and 900)
and (attribute_002 between 300 and 900)
and (attribute_003 < 800 or attribute_003 > 900) order by id;
rows self-join view virtual pivot table
---- -------------- -------------------
0 6.7 0.5 seconds
2 6.8 0.4
5 18.8 0.4
12 8.0 0.6
21 8.9 1.1
42 9.0 0.8
494 35.8 2.7