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'm trying to solve a performance issue and I need advice.

I have a table, which about twice a day has a massive surge of inserts coming in, and exactly at the same time everybody is trying to read from it. So I have locks and everything becomes painfully slow.

My performance requirements are as follows:

  1. many writers, from different sources, and i prefer each write to be atomic.
  2. i don't care about the order of the writes, number 7 can come in before number 3.
  3. writes should end ASAP, I want writers to wait the least possible amount of time.
  4. data is never updated, only inserted.
  5. I have many many readers.
  6. readers want to see the most updated snapshot possible at the time of their read.
  7. I can live with readers seeing delayed data (10-20 seconds)
  8. I need occasionally do complex and large queries on this data, so i need several indexes on it.

How would you approach this, and how would you implement this with SQL Server?


I'm thinking of splitting it into 2 tables... one for the inserts, and one for reading, and a worker moving data.

the large queries will run only on the second, the readers that need updated data, can run on the second, and complete what they need from the first.

share|improve this question
    
Are writes batched? Or single writes? Any lookup keys to resolve? How many is "massive"? –  gbn Jul 30 '13 at 14:09
    
writes can't be batched i don't have any other persistence for them. Today there are three indexes covering everything the complex queries require, and a clustered index for the regular readers. Massive is a stock exchange feed massive... or at least that's the goal. –  AK_ Jul 30 '13 at 14:15
    
Stock Exchange prices may or may not be volatile: is it hedge fund flash pricing? Or standard equity EOD prices? Numbers are better too. –  gbn Jul 30 '13 at 14:28
    
It's not prices, it's transactions and price changes. –  AK_ Jul 30 '13 at 14:43
    
Numbers for performance don't really matter, They are more than we can handle anyway we're going to have to do some sort of sharding eventually... –  AK_ Jul 30 '13 at 14:45
show 4 more comments

migrated from stackoverflow.com Jul 30 '13 at 14:03

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

2 Answers

One solution I used was to thread out writes to multiple staging tables, then collect the data in batches. We put the staging tables on their own filegroups to try and segregate the I/O as much as possible, though we did end up with two tables on each available LUN, as opposed to each table getting its own dedicated I/O.

Since you said order of writes doesn't matter, this may be viable, but I don't know if 20 seconds will be sufficient to make this work and still not encounter a bunch of blocking.

So let's say we have a base table like this, which is where everyone reads from (and is currently writing to):

CREATE TABLE dbo.StockData
(
  StockID INT,
  ...other info...
);

Then we have staging tables like this, where we're going to write instead:

CREATE TABLE staging.StockData0
(
  StockID INT,
  ...other info...
);

CREATE TABLE staging.StockData1
(
  StockID INT,
  ...other info...
);

... up to StockData9

*Don't bother mimicking all of the non-clustered indexes, constraints etc. on these staging tables,

Then assuming you have singleton inserts via a stored procedure, change the stored procedure to use dynamic SQL and insert into one of the 10 tables based on the StockID value:

DECLARE @sql NVARCHAR(MAX) = N'INSERT staging.StockData'
  + CONVERT(CHAR(1), @StockID % 10 + '(StockID, cols...)
    SELECT @StockID, @params...;';

EXEC sp_executesql @sql, N'@StockID INT, @params...', @StockID, @params...;

Then have a background job that performs a very quick, metadata operation moving these tables into a dummy schema - this minimizes the amount of time that is spent blocking users' ability to write while you move this data into the real table (which you can now control much better than sporadic writes from all over the place). So first create a second copy of the tables in a different schema:

CREATE SCHEMA shadow  AUTHORIZATION dbo;
CREATE SCHEMA holding AUTHORIZATION dbo;
GO

CREATE TABLE shadow.StockData0
(
  StockID INT,
  ...other info...
);

-- repeat for 1-9

Now the job would do this:

TRUNCATE TABLE shadow.StockData0;

BEGIN TRANSACTION;

  ALTER SCHEMA holding TRANSFER staging.StockData0;
  ALTER SCHEMA staging TRANSFER shadow.StockData0;

COMMIT TRANSACTION;

ALTER SCHEMA shadow TRANSFER holding.StockData0;

-- repeat for 1-9 (I actually used dynamic SQL in a loop
-- so I wouldn't have to repeat myself ten times...

I blogged about this part of it here and here.

The job would then go on to insert batches into the primary table, with a delay in between, to allow some reads to happen.

BEGIN TRANSACTION;

  INSERT dbo.StockData(StockID, ...cols...)
    SELECT StockID, ...cols...
    FROM holding.StockData0;

COMMIT TRANSACTION;

WAITFOR DELAY '00:00:01';

-- repeat for 1-9

This was several years ago, and something that didn't occur to me at the time (which I remember now, too, after seeing the other link posted by wBob) is that using TABLOCK inside those transactions may help speed up the inserts (though you'll want to test based on volume, isolation level, etc).

It might also be useful (even with all this work) to enable Read Committed Snapshot Isolation and/or if you are on Enterprise Edition considering partitioning (and aligning the staging tables to those partitions).

share|improve this answer
    
ALTER SCHEMA...TRANSFER ? Never heard of it. Interesting... –  Fabricio Araujo Jul 30 '13 at 18:43
add comment

The SQL CAT team posted an article on a similar sounding topic:

Bulk Loading Data into a Table with Concurrent Queries http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/04/06/bulk-loading-data-into-a-table-with-concurrent-queries.aspx

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.