Desiderata Errata

As a continuation of this performance comparison , I wanted to also compare performance in SQL Server's supposed strong point: reporting on very large relational data sets. Does it truly perform better than MongoDB? Again, this will attempt to be a mostly vanilla comparison, without the different optimizations that can be done in both platforms, for a couple of typical reporting queries. Setup The test box remains the same: Core i7 CPU, 16 GB RAM, 1 TB 7200 RPM data disk. SQL Server 2019, MongoDB 6.0.1. Data was randomly generated using a console app and inserted into both platforms. For SQL Server, I created both a relational table structure, and a columnstore indexed table that is more typically seen in reporting databases. The columnstore table was created as a flat copy of the relational tables, and a SELECT FROM INSERT statement populated the data. MongoDB received the same data, with the Customer and Product data embedded in each document to avoid doing any lookups or joins....

Anyone reading the title of this post may immediately start to question my sanity and/or competency. After all, SQL is a relational datastore and MongoDB is a document datastore, and wouldn't it just be obvious that a document datastore would be more effective at storing unstructured JSON documents? I have been learning more about NoSQL databases and that's the common wisdom, but I've been around long enough to doubt any hype over any technology. I want to know: how much better? Do the new JSON features in SQL Server 2019 compare well? I wanted to see how both systems perform when the rubber meets the road. Setup To do the comparisons, I'm using DICOM files as the data. The protocol details are too deep to get into here, but they're used in health care to transfer information and images between systems. They are distinct documents, with information stored in tags and sequences, and readily translate to a JSON format . Here's a full example . Updates are not rea...