Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this strange question. I'm building a service that stores information into one table. That table has 20 columns. My database will have many billion rows. Right now I'm using Cassandra, but it takes ~5 seconds to return 22k rows which is about 4mb in size.

I have tried to optimize it, but it's not that easy. People in #cassandra said that Cassandra isn't very good for my service.. So here I am now.

It would be very helpful if you could help me find a perfect database for me.

It would be nice if it would be scalable, could host billions of rows and would have fast reading speeds.

If you decide to downvote this question, please let me know why you did that.

Thank you!

share|improve this question

closed as unclear what you're asking by Phil, Paul White, Mat, ypercube, dezso Mar 11 at 10:20

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
You need to give us some kind of clue... What selectivity do the rows have? What kind of data is it? –  Phil Mar 10 at 22:26
    
There would be 3 primary keys and I would select all columns. Data is mostly numbers and about 5 columns would be text. Does this help? –  Jason Mar 10 at 22:32
    
I'm not going to downvote you, but your question is too vague. What are the data types? Record size? Purpose of what you are doing? One table is not a database, that's a file. Why aren't you using a spreadsheet? Text editor? Have you tried using MySQL or PostgreSQL? Benchmarked them? What are your long term goals? Again, question too vague. Paul... –  Vérace Mar 10 at 22:34
    
Data types are int and text. Purpose is data analysis. I can't use MySQL because it's too slow when I have over 100 million rows. I have not used PostgreSQL. My goals are to build real-time report pages of that data. –  Jason Mar 10 at 22:36
2  
Could you be more specific about your table structure? There are half a dozen kinds of ints on some dbs and at least as many different kinds of text columns. Huge text will potentially kill performance depending which db server and the column definition. There is a huge difference in performance for data warehousing and real time analysis. With the current state of databases, it is unlikely to get both in a single instance. –  Adam Zuckerman Mar 11 at 4:26

1 Answer 1

up vote 1 down vote accepted

It is not a strange question. I have the same problem every day; how to get things to run faster, faster. We use MS SQL Server 2012 with 512 GB RAM and a SAN at 50TB. We have about ten tables with more than a billion rows, our largest table is 75 billion rows.

What really helps is ensuring that the right datatypes are used; no bigints when tinyints will do. No varchar(5), when a char(5) is better, or rather no varchars at all in the fact tables. Keep varchars in the dimension tables.

If you are looking to aggregate data, you could look into using Analysis Services or some other sort of Cube. That will move the issue over to the writing bit of the process, away from the read bit.

Best regards,

Henrik

share|improve this answer
    
Thank you, that's a great answer. I think I'll go with MongoDB because it's oriented around reading which is what I need :) –  Jason Mar 11 at 9:45
1  
@Jason Perhaps you're not being clear about what you need and blindly going with some less-than-optimal solution. –  Kermit Mar 11 at 13:27
    
@Jason: probably not a bad choice, but I would still have a look at your table design, to see if your one table can be split into a set of tables, in order to reduce the table size. It is a lot faster for the database to read a small table (in GB rather than TB) –  Henrik Staun Poulsen Mar 11 at 13:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.