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.

Imagine a stream of data that is "bursty", i.e. it could have 10,000 events arrive very quickly, followed by nothing for a minute.

enter image description here

Your expert advice: How can I write the C# insert code for SQL Server, such that there is a guarantee that SQL caches everything immediately in its own RAM, without blocking my app for more than it takes to feed data into said RAM? To achieve this, do you know of any patterns for setup of the SQL server itself, or patterns to set up the individual SQL tables I'm writing to?

Of course, I could do my own version, which involves constructing my own queue in RAM - but I don't want to reinvent the Paleolithic Stone Axe, so to speak.

share|improve this question
1  
Are you talking about C# client code? So you're interested in SQL code that ensures writes are cached? –  Richard May 27 '11 at 8:28
6  
I be inclined to queue inserts myself EVEN if the RDBMS supports it because (a) it's not hard, (b) it's totally under your control, and (c) it ain't vendor dependent. –  corlettk May 27 '11 at 8:33
    
I'm interestedi in the C# client code that contains the SQL code to ensure writes are cached. However, I"m sure that I could work with the straight T-SQL and write my own C# wrapper. –  Gravitas May 27 '11 at 13:12
add comment

migrated from stackoverflow.com Feb 3 '12 at 14:58

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 10 down vote accepted

Have you tried just writing and see what happens? Do you have a known bottleneck?

If you need to prevent your app being blocked then you one way would be to queue the writes to defer the database call. However, I'd expect the queue to clear in a second or 2: so do you need an queue if this is OK?

Or you can spool to a staging table and then flush later? We use this technique to deal with sustained writes of millions of new rows per minute (we actually use a staging DB with Simple recovery): but we didn't implement it until we had experience of just writing rows.

Note: Every write in SQL Server will go do disk as part of the Write Ahead Logging (WAL) protocol. This applies to the t-log entry for that write.

The data page with the row will go to disk at some point (based on time, use, memory pressure etc) but generally your data will be in memory anyway. This is called "Checkpointing" and doesn't evict data from memory, just flushes changes (edited 24 Nov 2011)

Edit:

For throughout considerations, based on ther last paragraph above, shift your LDF for this database to a dedicated set of disks for more performance. Ditto a staging database (one each for MDF/LDF). It's quite common to have a dozen or 3 different volumes (via a SAN normally) for your database server

share|improve this answer
1  
Spooling to a staging table is probably the best way to go. I've also had confirmation from one of my friends, who works in an environment with billion row tables, he said he uses temp tables for faster analysis. –  Gravitas May 30 '11 at 21:37
add comment

Unless I'm missing something, this would violate the Durability requirement from ACID (http://en.wikipedia.org/wiki/ACID). That is, if your application "writes" the data to RAM and then the server crashes, your data is lost.

So, what you seek is either a non-database system that serves as a queue for eventual storage into a database or a database system that is sufficiently fast for what you're doing. I'd suggest trying the latter first and see if it's sufficient; don't borrow trouble.

share|improve this answer
    
+1 I should have mentioned this. WAL is required for ACID –  gbn May 27 '11 at 16:39
add comment

I used once a Dataset for this. I was inserting rows to dataset as they arrived, and there was another thread that was flushing the rows every 2 seconds or so to the database. You can also use xml document to do the cachin, and then pass the xml to the database in one call, this may be even better.

Regards

Piotr

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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