What is the best database for my needs?

Posted by Mr. Flibble on Stack Overflow See other posts from Stack Overflow or by Mr. Flibble
Published on 2010-03-24T11:59:39Z Indexed on 2010/03/24 12:03 UTC
Read the original article Hit count: 485

Filed under:

database

|

non-relational-database

|

relational-database

I am currently using MS SQL Server 2008 but I'm not sure it it is the best system for this particular task.

I have a single table like so:

PK_ptA PK_ptB DateInserted LookupColA LookupColB ... LookupColF DataCol (ntext)

A common query is

SELECT TOP(1000000) DataCol FROM table 
WHERE LookupColA=x AND LookupColD=y AND LookupColE=z
ORDER BY DateInserted DESC

The table has about a billion rows with 5 million inserted per day.

My main problem with SQL Server is that it isn't too easy to shard or spread out the datafiles. Also, exporting seems to max out at 1000rows per second (about 1MB/s) which seems very slow.

Another problem I have is, with SQL Server, if I want to add a new LookupCol the log file grows enormously requiring a large amount of rarely used free space on tap.

Are there any obvious better solutions for this problem?

© Stack Overflow or respective owner

Related posts about database

SQL SERVER Retrieve and Explore Database Backup without Restoring Database Idera virtual database

as seen on Dot net Slackers - Search for 'Dot net Slackers'
I recently downloaded Ideras SQL virtual database, and tested it. There are a few things about this tool which caught my attention.My ScenarioIt is quite common in real life that sometimes observing or retrieving older data is necessary; however, it had changed as time passed by. The full database… >>> More
Cloning A Database On The Same Server Using Rman Duplicate From Active Database

as seen on Oracle Blogs - Search for 'Oracle Blogs'
To clone a database using Rman we used to require an existing Rman backup, on 11g we can clone databases using the "from active" database option. In this case we do not require an existing backup, the active datafiles will be used as the source for the clone. In order to clone with the source database… >>> More
cPickle ImportError: No module named multiarray

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, I'm using cPickle to save my Database into file. The code looks like that: def Save_DataBase(): import cPickle from scipy import * from numpy import * a=Results.VersionName #filename='D:/results/'+a[a.find('/')+1:-a.find('/')-2]+Results.AssType[:3]+str(random.randint(0,100))+Results.Distribution+"… >>> More
SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot

as seen on SQL Authority - Search for 'SQL Authority'
Snapshot database is one of the most interesting concepts that I have used at some places recently. Here is a quick definition of the subject from Book On Line: A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and… >>> More
OTN ???? ?????? ???????

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Database ?? Database ??????? Database ?????????? Java WebLogic Server/????????·???? SOA/BPM/????? ???????/???? ID??/?????? ?????EPM/BI EPM/BI ??????? EPM/BI ???? OS/??? ???? ????? MySQL Database ?? ???? ?? ????????? ??? ?? ORACLE MASTER… >>> More

Related posts about non-relational-database

What are good NoSQL and non-relational database solutions for audit/logging database

as seen on Stack Overflow - Search for 'Stack Overflow'
What would be suitable database for following? I am especially interested about your experiences with non-relational NoSQL systems. Are they any good for this kind of usage, which system you have used and would recommend, or should I go with normal relational database (DB2)? I need to gather audit… >>> More
Non-relational database modeling tool?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hey guys, please recommend some tools you have used succesfully on DW, DataMart, BI an non-relational modeling. Example for automatic creation of snow-flake Schemas, dimensions and facts tables. Wich tools makes you sense familiarity with the diagrams and surrogates keys and it will have the option… >>> More
Non-Relational Database Design

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm interested in hearing about design strategies you have used with non-relational "nosql" databases - that is, the (mostly new) class of data stores that don't use traditional relational design or SQL (such as Hypertable, CouchDB, SimpleDB, Google App Engine datastore, Voldemort, Cassandra, SQL… >>> More
Is it easy to switch from relational to non-relational databases with Rails?

as seen on Stack Overflow - Search for 'Stack Overflow'
Good day, I have been using Rails/Mysql for the past while but I have been hearing about Cassandra, MongoDB, CouchDB and other document-store DB/Non-relational databases. I'm planning to explore them later as they might be better alternative for scalability. I'm planning to start an application… >>> More
SimpleDB as the main database, examples.

as seen on Stack Overflow - Search for 'Stack Overflow'
Please, can you give examples of real-life implementations using SimpleDB as the main persistent storage? >>> More