 Converting to Column Oriented Architecture [message #530813] |
Thu, 10 November 2011 14:47 |
eeriehunk
Messages: 16
Registered: May 2009
|
Junior Member |
|
|
Hello All,
This is more a generic question towards Row-Based vs Column-Oriented Architecture and how to convert to a Columnar structure, so kindly bare with me on the literature.
I do like to give an scenario on why this tweaked my curiosity.
I work a lot on developing queries for reports which require lot of aggregation function like SUM.
Unfortunately I cant pre-aggregated them as the input Filters from the UI side can change.
For example: Say I have 5 filters (City, Gender, age, group, company_code)on the UI. The user can select any number or combinations of filters. Not necessary that a value has to be added to each filter. So, say the user selected, City = Omaha, Gender = M and Company_code = 2.
Based on the above I formulate the following query :
select Year, month, day, sum(visits), sum(cost), sum(visitors)
from visitors_tab
where city = 'Omaha' and gender = 'M' and Cmp_code = 2
group by Year, month, day;
Only the where clause changes based on the Filters selected. say if age is selected as 20+ then you will see another addition to where clause as "and age > 20".
I have very slow query performance so I was considering Column Oriented Architecture VS Row architecture as the table has 60 columns and I am only aggregation say 10 of them.
Question: How to convert a regular table to a column style.
Will that improve performance. If so how.
And guys, I have already tried partitions on Cmp_code with city and gender local bitmap index, and it improved performance for a 50 sec query to 25 sec. But I need a query that runs less then 10 sec.
I did research about Column Oriented ..but they explain what they are but could not find a way to convert a regular table to columnar structure tables and test performance.
Thank you,
Aj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
