Thank you Robert,

Excellent article.
In fact we use this approach when we need to gain speed for queries that run on hundreds of millions records indexes and need to check few items only, so we save checking the "base" class by using [ DATA ... ]  

In addition, to gain more speed, which is so essential in huge queries, we use the (good, old) $order on the index global itself to gain more speed. This is much faster than normal SQL. 

Using $Extract will be 17 % faster (!)
Set date=20201121090000  
Set newDate=$E(date,1,4)_"-"_$E(date,5,6)_"-"_$E(date,7,8)_" "_$E(date,9,10)_":"_$E(date,11,12)_":"_$E(date,13,14)
 

In my opinion, it is much better & faster to store binary files out of the database. I have an application with hundreds of thousands of images. To get a faster access on a Windows O/S they are stored in a YYMM folders (to prevent having too many files in 1 folder that might slow the access) while the file path & file name are stored of course inside the database for quick access (using indices). As those images are being read a lot of times, I did not want to "waste" the "cache buffers" on those readings, hence storing them outside the database was the perfect solution.

There are few considerations here:

1. If you are using a failover (sync) mirror, you will need to that Ens.* data on the backup so a switch can be done seamlessly, and Ensemble production can start from the exact same point when failure occurred.

2. mapping any globals into a non-journal database will still have them on the journal when the process is "in transaction". Ensemble production is writing everything "in-transaction"

3. Mapping Ens.* to another DB is good idea if you have several disks, and you want to improve I/O on your system (specifically within cloud VMs that usually have a bit slower disks than on-premise). Another option is to let the system purge Ensemble data regularly and maybe keep less days of history.

4. Mirror journals are regularly being purged after all sync/a-sync mirror members got them.

There is a stand alone studio called "IRIS studio" that can be installed independently (no need for IRIS install).

If you download the latest version it will support "backward" all versions of Cache/IRIS 

consider also that even when you use the DISABLE^%SYS.NOJRN if you are within a transaction globals will be saved (e.g when doing a %Save() on an object).

To prevent this you may use:
$system.OBJ.SetTrandsactionMode(0) - to disable transaction
$system.OBJ.SetTrandsactionMode(1) - to enable transaction (leter)

what is the vet.numConselho for those 2 records ?

quine ; quine routine is a routine that prints itself content
X "ZL "_$T(+0)_" ZP"
Q

DB tuning has many aspects:
1. When the DB is a SQL one (persistent classes) than as some said a "tune table might help, but this will help mostly with SQL queries, by optimizing the use of (correct) indices.
There are other SQL tools on the portal (query plan, sql index usage etc.) that might help to see if the indices are correct.

2. Using the correct data types for your data (e.g. using %Date data type to store dates in $horolog format is much more effective than a %String (YYYY-MM-DD) on large scaled databases, especially with indices where cache memory can not hold most of the DB or with systems with huge number of transactions).
BTW, this is true to both persistent classes (that you access qwith SQL) and "raw" globals that you access with COS (or other language).

3. In a large scald DBs if some of your queries are "heavy" - code optimization might also be a consideration (e.g. replacing SQL with COS that does direct $Order on the index globals).

Hello,

Will the "Built-in license for InterSystems SAM Manager" be effected as well ?

the "Expiration Date" is 0 - so i guess it is not, but just want to be sure ...

Very good article, Laurel.

To me, as one that has many years of experience with software development, it is obvious.

However, I also learned in my career that a good & precise design of your data model, using correct (and compact) storage of values, building the relationships correctly (in case of OO approach), using proper indices & fine tuning queries (in case of SQL approach) can make a huge difference between a "normal" and "high-end" systems.
All those become a major concern when we are talking about a very "high volume" systems that needs to process hundreds of millions of transaction per day.

Going to "compact" data model with a reliable code will save you also a lot in your capacity planning both in a cloud hosting or on premise.

Here is a test for running all those codes. 
It looks like that the 1st solution (with $translate) is the fastest.

Hello Peter,

Welcome back to the community.

I also have almost ~30 years (since 1991) experience on Intersystems technology starting with some PDP11, VAX & Alpha machines (anyone else misses the VMS OS like I do?) with DSM, the first PCs with MSM and then (1999) came Cache that become IRIS.. very long, interesting and challenging way !

I do remember at 1992 customers with 50-100 users running MSM on a 286 machines. and it was fast (!) At that times, where disks were slow, small & expensive, developers used to define the globals in a very compacted & normalized way, so applications flew on that platform.

Today, when disks (as Rebert said) are without limits, fast & relatively cheap, some "new" developers tend to disparage with a correct design of a DB, which on a big databases of few TBs it is noticeable.
You can get a significant improvement when the are been optimized (this is part of my job in the past few years). and this is not just the data structure, but also in correct way of optimized coding.

In my opinion, the multi-level tree of blocks that hold a global, is limited by the DB itself, which is limited by the OS file system. With global mapping, and sharding (new technology) - even a DB in a size of PETA bytes coukld easily hold them, and be a very efficient, like this technology was forever.

All answers are correct.

I personally prefer the "array of %String" approach, which is much more efficient and gives a better performance (in case your table grows to a huge size of hundreds of GBs)

Actually I was always using $Zorder ($ZO) which was "invented" 30 years ago, before $Query (popular MSM, DSM etc.)
Another thing is that $Order is a "vertical" way of looping through an array/global/PVC and $Query (or $ZO) are meant to loop in a "horizontal" way (same as you ZWRITE it)

it has the same functionality, and very easy to use:
Set node = "^TestGlobal(""Not Configured"")" W !,node
^TestGlobal("Not Configured")
F  S node=$ZO(@node) Q:node=""  w !,node,"=",@node
^TestGlobal("Not Configured","Value 1")=value 1
^TestGlobal("Not Configured","Value 2")=value 2

Very nice article Robert. 

From our experience with Embedded Python (we have a working solution to connect IRIS directly to ASB) we found that it is much faster to pass the Python object to COS and let the COS handle all global activity 

Hello,
i've added an article for using embedded python with ASB (Azure service bus)

https://community.intersystems.com/post/iris-embedded-python-azure-serv…

I recommend you to check the WebTerminal, which you may connect to VS-Code .. 

https://intersystems-community.github.io/webterminal/#docs

Adding a soft delete is a good idea, but then indices will have to be changed as well to support that.

If all your 5 places of code are not called, and record are keeping "disappear" then it might be a SQL that is run by a user or developer. I would recommend to :

- have an detailed audit on that table/class to see it's deletions
- check all ODBC/JDBC users - to see if permissions for delete can be removed
- Possibly to have a code to scan journal files, to find that class, global, pid, date time stamp - and store this on a sperate table or global that can be later examined

I'm using this also after a minor update. It is better to have it like:

So it will do a recursive compile