using SQL server to store data

Question

I'm right now using SQL server 2008 in my project to store and fetch data . this is going perfect till now . I can fetch 20000 records in less than 50ms (JSON) . but facing a problem with inserts stuff . in my project I need to be able to insert something like 100000 records every minute . and this is seems to be very slow with SQL server .
I've tried to use another database (NOSQL DB) like mongoDB which are very fast in storing data (5s) comparing to SQLServer(270s) but not fast as sql in fetching data(20000 => 180ms) .
So I'm asking here if there any way to make SQL faster in storing . or to make mongoDB faster in fetching ( I'm not an expert in mongoDB I know the very basic things about it ) .

public static void ExecuteNonQuery(string sql)
{
    SqlConnection con = GetConnection();
    con.Open();
    SqlCommand cmd = new SqlCommand(sql, con);
    try
    {
        cmd.ExecuteNonQuery();
    }
    finally
    {
        con.Close();
    }
}

SQL's Insert function

public IEnumerable<T> GetRecords<T>(System.Linq.Expressions.Expression<Func<T, bool>> expression, int from, int to) where T : class, new()
{
    return _db.GetCollection<T>(collectionName).Find<T>(expression).Skip(from).Limit(to).Documents;
}

Mongo's Select function ( MongoDB 1.6 )

Update : data structure : (int) Id , (string) Data

How big are the strings you typically need to store. Are there indexes on the table (which need to be updated during insert). Can you use SQL's Bulk INSERT command (very fast compare to straight inserts) — Sparky
– Sparky, Commented Feb 19, 2011 at 12:20

Anders Zommarin · Accepted Answer · 2011-02-19 12:19:28Z

4

I guess that you are executing each insert in a transaction of its own (an implicit transaction might have been created if you do not provide one explicitly). As SQL server needs to ensure that the transaction is committed to the hard drive each transaction has a overhead that is very significant.

To get things to go faster, try to perform many inserts (try with a thousand or so) in a single ExecuteNonQuery() call. Also do not open and close, but keep the connection open (thus being in the same transaction) for several inserts.

answered Feb 19, 2011 at 12:19

Anders Zommarin

7,3142 gold badges27 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Magnus · Accepted Answer · 2011-02-19 12:29:04Z

3

You should have a look at the SqlBulkCopy Class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

answered Feb 19, 2011 at 12:29

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

1 Comment

Anders Zommarin Over a year ago

Very good suggestion. However bulk copy does not have the same semantics as a "standard" insert operation, so it might not fit your need.

user2665694 · Accepted Answer · 2011-02-19 12:21:05Z

0

MongoDB is very fast on reads and writes. 50k reads and writes per second is doable on commodity hardware - depending on the data size. In addition to that you always have the option to scale out with sharding and replica sets but as said: 20k operations per seconds with MongoDB is nothing.

answered Feb 19, 2011 at 12:21

user2665694

Comments

TonyD · Accepted Answer · 2011-02-19 12:21:51Z

Generally the speed on inserting data into the database is a function on the complexity of the operation.

If your inserts are significantly slow, then it points to optimisation problems with the inserts. Identify exaxtly what SQL insert statements your program is generating and then use the database EXPLAIN function to figure out what operations the underlying database is using. This often gives you a clue as to how you need to change your setup to increase the speed of these operations.

It might mean you have to change your database, or it might mean batching your inserts into a single call rather than inserting each item separately.

I see you are setting up and closing the connection each time.. this takes a significant time in itself. Try using a persistent connection.

Collectives™ on Stack Overflow

using SQL server to store data

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related