I tried Cosmos. Still 2 magnitudes slower.

Haven't tried Mongo yet, but I don't expect it to be faster.

Preaggregation sounds familiar 🙂

Well... Do that for 100+ attributes and you need 100+ indexes.

And still you can't use more than one index when searching for

WHERE treatment = @treatment AND bp =

Yes.

Not only because of the simplicity, but for the streamlined storage need and search optimization. You only need one index for all attributes.

The EAV model is extremely well suited for...

I just realized that both Jonathans and Jeffs queries used all 8 cores and mine just used 1 core.

So here is the same query with parallell plan

SELECT...

The timings on my laptop

-- Jonathan
Table 'PATIENTS'. Scan count 18, logical reads 3494, physical reads 0, page server reads 0, read-ahead reads 1754.
Table 'Worktable'. Scan count 0,...

SELECT          FIRST_NAME,
                LAST_NAME,
...

• This reply was modified 1 year, 5 months ago by  SwePeso.

Is it something this you want to achieve?

WITH cte_mockup_calendar(the_date)
AS (
        SELECT  DATEADD(DAY, value, '20230101')
     ...

Here is a "one query" solution 🙂

Finished in about 10 seconds for prime numbers up to 1 million.

DECLARE @max_prime INT = 1000000;

-- swePeso
SELECT  value
FROM  ...

A simplified version is found here

https://www.sqltopia.com/mathematics/prime-number-generation/

A simplified version is found here

https://www.sqltopia.com/mathematics/prime-number-generation/

The function returns the correct date between year 1900 and 2203.

Probably something with

If exists(select * from inserted)

DECLARE @sample TABLE
        (
                BinaryValue...

Keep it simple

DECLARE @sample TABLE
        (
              ...