Insert streamed RFID data

Question

I have RFID readers currently writing data to files on a hard drive, which then SSIS reads and inserts into the database. Instead, I'd prefer that SQL Server (2016+) read the data stream directly into a Memory Optimized Table to increase performance and handle scalability.

What is the best method to do this, handling multiple readers sending data all at once, and making sure no data gets lost due to bottlenecks, deadlocks, etc.?

Each reader can handle approximately 60 reads per second, then transmit via wi-fi (or later via USB cradle). So per minute, we're talking 60x60x512bits of data per reader. For future, assume in large warehouses that there could be 10+ readers going at once.

There is simultaneous processing and reading of the data. While the reader data is coming in, it needs to be checked for inconsistencies, redundancies, de-duped, updated (e.g. if a product location has moved), etc. Then, queries / reports will be made against the DB - various user searches, inventory and location reports, analysis.

Also handling of bottlenecks due to simultaneous delivery of data by readers - i.e. say 5 readers were delivering their daily data at the same time through cradles, while the DB was doing various other tasks.

Answer 1

How confident are you that max concurrent connections will stay at 10 in the future?

If you're interested in raw speed - which is part of the domain of In-Memory OLTP - then transmitting via wifi will likely be a bottleneck. Even if you're mainly interested in scalability of the write workload - wifi or USB cradle will have its limits - preventing the full (potential) benefit of using In-Memory OLTP.

While Tony Hinkle is correct that an IDENTITY is more "efficient" than a GUID, clustering on an IDENTITY is an anti-pattern for highly concurrent, write-intensive workloads. It's exactly this anti-pattern that In-Memory is meant to solve, but consideration of using In-Memory OLTP should start a dialog that takes into account all of the many gotchas, i.e. no db mirroring, no snapshots, can never remove the memory-optimized filegroup, etc.

Queries can produce different results when using any form of snapshot isolation, so you'd have to verify that the way your app works can handle that.

So while the volume of data to be inserted is not particularly high, if you throw enough concurrent connections at it, scalability will be an issue. This is easily demonstrated with ostress (as long as you don't output to files). As concurrency ramps up, say from 100 to 500 connections, INSERT performance simply crawls. This is due to having to latch the last page of the clustered index, which essentially serializes all writes to the table.

Given what you've described, In-Memory OLTP might be best used like a "shock absorber", as a landing pad for your data. Then you could validate it, before sending it to its final destination. If that final destination is another memory-optimized table, be advised that all writes to a memory-optimized table are serial, no matter what the source. So you would have to roll your own parallelism, and ensure that your processing does not step on its own toes. You'll essentially have a queue, and due to its lock-free architecture, In-Memory OLTP is poorly suited to queue processing. There must be some form of serialization to make sure your concurrent processes don't attempt to process the same set of rows.

All traditional/disk based tables can suffer from deadlocks, which can usually be reduced, and perhaps eliminated, but there is no guarantee of that. Deadlocks are not possible with memory-optimized tables, due the lock/latch free architecture (same for indexes on memory-optimized tables).

Queries will probably not be any faster with In-Memory OLTP, as it's not designed for that.

I have blogged extensively about In-Memory OLTP. If you are seriously considering deploying it, I strongly suggest you study what I've written.

Answer 2

The formula you provide in the comments (60*60*512 bits) indicates that each reader will be inserting 230 kilobytes of data per minute. This is a rather insignificant amount of data. The only way you would have a problem with this if you had misconfiguration or horrible code of an epic scale. There is no need to bother using a memory optimized table. No reason to be concerned about locking and blocking or anything else. You could put SQL Server on the cheapest laptop you can buy with 2 cores and 4 GB of memory and it would be able to keep up just fine.

So with the rate you quote, for 10 readers that is a little over 3 GB of data:

60*60*512 = 1,843,200 bits
1843200 / 8 = 230,400 bytes
230400 * 60 (minutes) * 24 (hours) = 331,776,000 bytes per day (331 MB)
331 * 10 (# of readers) = 3.31 GB

So either we're missing an important part of the question, or you have absolutely nothing to worry about.

As to your concerns about blocking, etc., there is no simple answer. If the database and queries are designed properly, then you're good to go. If you don't know where to start with proper database design, then you might look into getting a consultant to do it. If you use read committed snapshot, readers won't block writers, and vice versa. I wouldn't worry about INSERTs being blocked with the volumes of data you are referencing--they should be so fast that any blocking will be insignificant, assuming, of course, that the database designed doesn't have serious flaws. For INSERT efficiency, the primary concern is making sure your primary key is a sequence number and not a GUID or string of some sort.