For a case with one index there are only two variables:

• Number of distinct values
• Number of total records.

Or is there anything else?

And on a Z axis the timing.

Sure in a real environment we see interference of a several indices, but here we're talking about one index and how it affects query timings.

Use Inventory utility.

It records classes and their hashes. Compare two inventory scans to get changes.

Ok, then, why it is so important, why I it is not recommend to use bitmap in case of so many unique values?

Bitmap indices lose efficiency with a big number of distinct values.

And what should be used instead?

Normal indices.

Actually the question gathered a fair amount of interest. I think I would run tests and publish the results.

My plan is:

1. Create a class with two properties:

Property Number As %Integer(MINVAL=1, MAXVAL=<DISTINCT VALUES COUNT>);

Property Data As %String;

2. Increase MAXVAL up by one from 2 to 20000;

3. Repopulate the class with 10 000 000 values.

4. Switch between Normal and Bitmap indices

5. Rebuild indices.

6. Purge queries.

7. Tune table.

8. Remount the database to purge cache.

9. Run two queries 10 times:

• select id, by random condition on Number
• select data by random condition on number

10. Write results into new table { distinct values, index type, cold run, avg run, max run}

11. Go to 2.

Does that pest plan makes sense? Any ideas? Should probably test on cases where index fits into globuf and where it does not.

2% or more means less than distinct 50 values.

Thank you, Robert! New value for my list. I think I got 64 once but that's a new one.

UPD: Nevermind this comment.

That's the number I hear most often (5-10k rang) when I ask about maximum number of unique values for bitmap indices.

I heard from a few hundred to 20k as a max value.

Check ^%ZSTART routine. And ^%ZSTOP for shut down.

Thanks!

I see it still uses $zf(-1) in latest instead of $zf(-100). I thought $zf(-1) is unavailable now.

Is there an easier way to get value from OS call then Input redirection?

Windows also has Ver command:

USER>$Ver

Microsoft Windows [Version 10.0.17134.345]

If it's a REST/CSP so you can just write the response to the current device add at the beginning of your method:

set %response.ContentType = "html"
do ##class(%CSP.Utils).DisplayAllObjects()
return $$$OK

It would output all headers as Cache sees them.

Have you tried:

write %request.GetCgiEnv($zcvt("HTTP_MyCustomHeader", "U"))

Note that you should build bitmap indices only for properties that have less than ~6400 distinct values. Also building indices may take time. Don't forget to recompile embedded SQL and purge dynamic SQL queries.

As I'm the author of RESTForms, I'd recommend that. The advantage is that it offers not only JSON<->object transformation, but a complete CRUD REST API for your data.

Calling @Stefan Cronje

All approaches you are considering can work. if you're storing everything in one database, I'd recommend RLS.

Much more important question is do you need storing everything separately or in the same place.

Advantages of separated databases/servers:

• Easy to scale
• Would work faster on a lot of cases
• Easy to delete/rebuild a chunk
• Easier security

Advantages of unified database/server:

• Cross-chunk queries are easier (For example: This venue is sold out on the dates you need. We recommend the following nearby venues)
• Simple Backup/HA strategies