Caché Data Access Modes

Since I have joined InterSystems a while ago, I thought a Caché post might be appropriate, especially since it illustrates quite nicely the various data access modes offered by the platform.
Below is a routine called TestModes.mac, invoked by a >do ^TestModes. The SQLCompile macro is necessary because the SQL code references objects which do not exist compile time, before CreateTable is run (InsertData). The multi-dimensional sparse arrays created as SQL tables (by CreateTable) are accessed in multi-value mode by ReadMV and deleted by DropMV (which also uses the DeleteExtent method of the data objects to perform a ‘table truncation’ on the data objects).
Another nice feature is the extraction of the list items from the arrays (table data rows are stored as array items, whereby each field is stored in a string list created by $LISTBUILD) back to strings by the $LISTTOSTRING function.
Some legacy features are visible (the need to declare SQLCODE as a public variable and make it NEW to restrict it to the current scope). I don’t think that this kind of mix would make sense in an application, where it would make sense to use one access metaphor and only one for a given data item; but this is still a valuable example which ties the data architecture(s) of Caché together nicely.

TestModes
; shows the different data access types
; SQL, MV, class
#SQLCompile Mode = DEFERRED

DO CreateTable
DO InsertData(1000)
DO ReadMV
DO DropMV
WRITE $$ReadData()
QUIT
;
CreateTable()
{
&sql( DROP TABLE items)
&sql( CREATE TABLE items(id INT, data VARCHAR(50)))
QUIT
}

InsertData(upto)[SQLCODE]
{
#DIM x as %String
#DIM i as %Integer

NEW SQLCODE

FOR i = 1:1:upto
{
SET x = i _ " DataItem"
&sql( INSERT INTO SQLUser.items(id, data) VALUES( :i, :x))
IF (SQLCODE '= 0 )
{
WRITE "SQL error:", $ZERROR, !
}
}
QUIT
}

ReadMV()
{
#DIM ky AS %String
#DIM extr AS %String

WRITE "Navigating the data", !

SET ky = $ORDER(^User.itemsD(""))
WHILE( ky '= "")
{
; WRITE ky, ^User.itemsD(ky), !
SET ky = $ORDER(^User.itemsD(ky))
IF ( ky '= "" )
{
SET extr = $LISTTOSTRING(^User.itemsD(ky))
WRITE ?5, extr, !
}
}
QUIT
}

DropMV()
{
DO ##class(User.items).%DeleteExtent()
KILL ^User.itemsD
QUIT
}

ReadData() [SQLCODE]
{
#Dim result AS %String
NEW SQLCODE

&sql(SELECT COUNT(*) INTO :result FROM SQLUser.items )
; the table is still there, but empty

IF (SQLCODE '= 0) SET result = "Error " _ $ZERROR
Q result

}

More on ORM; optimization?

Thinking about Cache and its 'native' objects, I wonder if when those are persisted to disk, only the data is actually saved, or any functions as well - i.e., p-Code compiled ObjectScript or Cache Basic member functions. Or perhaps even (class-query) SQL. All this code could be optimized for the given state (properties) of the object.

Then the question becomes, where is this data saved - perhaps in some raw extensions of the sparse arrays that hold the object member data.

Another interesting aspect (related to the sparse array storage system) is the kind of optimization, if any, that occurs at the SQL relational engine level. If there is optimization of any kind done at the I/O-sparse array level, this might conflict with the SQL optimization. Interesting stuff.

Which brings into question, is the optimization cottage industry a by product of the relational model? I have always found Oracle's optimization 'strategies' (the thick books dealing with that) somewhat ludicrous and antiquated. In order to do that really well, you need a deep understanding both of data and of sorting algorithms; with so many intervening layers (physical design, I/O patterns), even that understanding is corrupted. So if you can avoid a couple of grievous errors (e.g. multiple joins on non-indexed columns), you will do reasonably well. But then, the DBMS should be able to detect if you're about to make a grievous error (or perhaps the reporting tool, if you use one). So, why a thick book on optimization?