3 ms·

For a project where I needed to quickly partition a data tale of millions of rows at multiple levels, I wrote a column database with bitmap index for data stora

For a project where I needed to quickly partition a data tale of millions of rows at multiple levels, I wrote a column database with bitmap index for data storage. It is 100% c# code. The queries and data manipulation is lighting fast the data loading is a little bit slower than traditional db because all the index that needs to be created on the fly, but once is done, is a sweet ride after. Things that I learned to consider, the quality of the compression algorithm, there is a trade off between compressing and some specifics type of queries. Also, the cardinality of the column ( number of different values in the column ) will impact the indices that need to be created and the query time response, if the cardinality is too high, b-tree might be a better alternative. I was working towards making a product out of it, but then I saw SQL Server new column index and I drop it. I don't think anybody would be interested in a little dll, when the big boy is offering the same functionality.

siganakis 15y ago
I would be very interested in hearing more about that project, as it sounds very similar to what I am working on for my cancer research. The two problems I keep running into are that run length encoding means you have to read the entirety of the bitmap to get to the value you want, and looking up a particular value for a particular row (where you have to scan all the bitmaps for that column) - which as you state makes high cardinality columns slow. I would be very interested in learning about ways to overcome these issues. Hit me up at terence [at] siganakis [dot] com if you would like to talk more, or on twitter at @siganakis (since I can't find any contact information in your profile). Cheers
baconner 15y ago
I'm pretty sure the new column store indexing in SQL server is enterprise edition only (not even in the new bi license) which in my experience prices out a lot of small businesses. Might be a niche in there somewhere to be served.
- jorgeleo 15y ago
  Actually I am observing this news... and how the licencing for SQL Server will end up panning out. Some people here still thinks that row oriented databases can compete in aggregation speed, so if I do a MVP it will require a lot of teaching.

siganakis 15y ago

I would be very interested in hearing more about that project, as it sounds very similar to what I am working on for my cancer research. The two problems I keep running into are that run length encoding means you have to read the entirety of the bitmap to get to the value you want, and looking up a particular value for a particular row (where you have to scan all the bitmaps for that column) - which as you state makes high cardinality columns slow. I would be very interested in learning about ways to overcome these issues. Hit me up at terence [at] siganakis [dot] com if you would like to talk more, or on twitter at @siganakis (since I can't find any contact information in your profile). Cheers

baconner 15y ago

I'm pretty sure the new column store indexing in SQL server is enterprise edition only (not even in the new bi license) which in my experience prices out a lot of small businesses. Might be a niche in there somewhere to be served.

jorgeleo 15y ago
Actually I am observing this news... and how the licencing for SQL Server will end up panning out. Some people here still thinks that row oriented databases can compete in aggregation speed, so if I do a MVP it will require a lot of teaching.