7

We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, search and aggregate these transactions, fast reads would be very nice.

We want to guarantee very fast writes, and adding indexes to cover some of the reads would slow down writes.

The good news is that we can afford stale data. I was considering to create a copy of the data to a read-optimized table (with indexes) every n minutes, which also allows for a periodical bulk-copy (thus limiting the number of ops?)

I'm looking for an opinion on whether this is a valid strategy. If you consider this a decent strategy, do you have implementation options pointers? If not, what are the alternatives?

  • 4
    What is version and edition of SQL Server I guess AlwaysON could be a solution here If I read your question correctly. – Shanky Feb 8 '16 at 14:03
  • Do you mind posting the table and where you would like indexes. By transaction to you mean the insert is wrapped in SQL transaction or it is a "financial transaction" that is represented by a single line. – paparazzo Feb 8 '16 at 16:17
6

For SQL Server 2014 and later my recommendation is rather radical: switch to a clustered columnstore index. 1000 records/min is well within the range of columnstore bulk load capabilities, on even modest hardware. See Clustered Columnstore Index: Data Load Optimizations – Minimal Logging and SQL Server clustered columnstore Tuple Mover. The query performance of a clustered columnstore is quite staggering due to the inherent advantages of columnar storage and batch processing. But even more so on time series (which your data probably is), due to very likely segment elimination.

On SQL Server 2016 there are some specific improvements targeting your case, see Real-Time Operational Analytics Using In-Memory Technology and Speeding up Business Analytics Using In-Memory Technology.

For SQL Server 2012 and earlier my recommendation is to upgrade to 2014 or 2016.

In any case, I would shy away from Transactional Replication, for two reasons:

Also need to consider that log shipping or AlwaysOn readable secondaries can only offload the processing of querying, but not the schema requirements (ie. indexes). Any index required by querying on the replica would have to be created on the original DB, and the price is paid at write time.

Of course I assume you did due diligence and your writes are optimized now, ie. all the advice in The Data Loading Performance Guide is applied and your upload is bulk and minimally logged.

  • 4
    Note this assumes Enterprise – Aaron Bertrand Feb 8 '16 at 15:56
  • I've been reading up on it, and still have some reading left to do, and this looks like a great fit, but we currently only have a Standard edition license, and the Enterprise license is quite expensive :-/ – JefClaes Feb 8 '16 at 20:27
  • For a budget you can try Hadoop Hive stream ingest and and Hive querying – Remus Rusanu Feb 8 '16 at 20:34
  • 1
    But at the end of the day, 16 inserts per second is nothing to be worried about that "indexes will slow down writes". – Remus Rusanu Feb 8 '16 at 20:35
2

Without touching on the obvious hardware possibilities and HA solutions, I would consider building a "staging table" which is minimally indexed or even a heap, where you could offload incoming transactions with maximum performance.

A scheduled/recurring process could then asynchronously move that data into the main fact table, which could have indexes that are more suitable for reporting. The same process could also maintain aggregates in another table, so you could build reports directly on those aggregates. The key is asynchronous, so I wouldn't use triggers or indexed views, but rather something like a SQL Server Agent job that runs a stored procedure over and over.

Pros:

  • Lightning-fast inserts (hardly any waiting time at all when writing transactions)
  • Larger number of rows inserted per batch in the fact table, should provide better write performance
  • Allows for better/more indexes on fact table
  • Aggregate tables would provide pretty good reporting performance

Cons:

  • Staging table might be locked for a short period when it's polled by the asynchronous process.
  • Slight delay from the insert until the data is available.
  • Added complexity

Oh, and if you're on SQL Server 2014/2016 Enterprise Edition, your staging table could be in-memory.

  • For the async process consider cdc as it runs off the transaction log, so doesn't lock your staging table (you can then add indexes to the cdc table to optimise reads) – Andrew Bickerton Feb 9 '16 at 23:54
2

If it's just a single table, think about the idea of having a second copy of the table used just for reporting purposes. I wrote a two-part series about my solution here: Part 1 | Part 2.

Essentially you have a table that represents a copy of your transactional table, but it is optimized for your reporting workload (as such, perhaps it only has a subset of columns, a subset of rows, and part of that process can be creating completely different indexes on the read copy of the table - though that will add time to the process).

Every N minutes, something slightly less than whatever reflects your definition of "stale," you populate a second copy of this table (in a different schema, or with a different name) with the fresher data from your transactional table. Once it's been populated, you can start a transaction, swap the tables (name or schema), and then commit. The next person to read the data from the copy will get the fresher data.

How long the lazy background load takes doesn't really matter, since you've already conceded that stale data is okay. You do have to be sure to associate permissions with both copies, though, and statistics might also need to be a part of the process.

1

1000 transaction per minute
= 16.67 / second
= 480,000 / 8 hr day

16.67 / second is not the fast. I am getting over 100 / second on just a regular active big table.

Pick your PK or at least one index that you can sort the incoming data by so you have minimal fragmentation of that index.

If you can hold records to insert 100 or 1000 at a time and insert them sorted. One insert of 100 records is much faster than 100 inserts of one record each. Have timer that they are insert at least every x seconds.

On the other indexes pick only what you need. Give them a fill factor of like 50. You would be amazed at how much slower fragmentation takes place if you leave some space with a fill factor.

Perform index maintenance daily.

Yes you may very well need to get more exotic but 1000 / minute is not that big. Even if you do get more exotic index design that minimized fragmentation is still a good thing.

1

I think Daniel's answer is probably better than mine, but just to give you the basic alternatives:

Transaction replication with only that table replicated, to a different server.

Pros:

  • Instant, readable data
  • Read locks will only block the replicated server
  • Transactional replication is read through your transaction log, with an Agent reading all transactions, generating a script which is sent to the distribution database

Cons:

  • Inserts all data as Row-By-Row into your replicated database (slow)
  • Requires a new instance/server
  • Requires much more maintenance/ DBA time, along with extra steps to recover (generate new snapshot, etc)
  • Extra overhead of maintaining distribution database
  • Network latency will cause replication latency, making replicated database slightly out of date

Log Shipping (Secondary database that is receiving transaction logs should be in standy to allow reads)

Pros:

  • All tables recovered successfully to new instance, allows your queries to hit any table
  • No impact to primary server

Cons:

  • Size of destination database will always be equal to main database
  • Network traffic, if sent off-site
  • users will be kicked off when the logs are restored
  • As stated above in another answer, Log Shipping cannot really serve this purpose. The shipped table will have identical schema to the original table, and the original poster wants different indexes in his "stale" table. Log Shipping would require those indexes to be in the "live" table too. – Ross Presser Feb 10 '16 at 18:01
1

Do you need to handle this directly in the database? My inclination, since you're ok with slightly stale data, is to cache individual query results, rather than the table as a whole, in a layer like memcached or redis.

This is a pretty standard approach in web application development. The primary downside is that it requires development effort on the application, which may not work for your particular situation (but we don't know given what you've told us).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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