· Jun 12, 2017

Is Caché SQL essential to Caché Objects?

I would like to start a discussion regarding Caché Objects and Caché SQL.

It is my understanding that the creators of Caché Objects see Caché SQL as the reporting arm of Objects and as such SQL is essential to Caché Objects.

I once met a Caché Objects programmer who was writing code to $Order through the Globals because that person thought that Caché SQL was too slow and inefficient. I attempted to convince the person otherwise.

So, what say you? Is SQL essential to Caché Objects?


Is Caché SQL a nice to have but we can do without? Is it too slow? Is it too inefficient?

I really would like to have an idea of what the InterSystems Caché Community, as well as the InterSystems Engineers and anyone else with skin in the game thinks.

Discussion (29)4
Log in or sign up to continue

I guess it depends on which context you're referring to essential.

If you are working on a pure Cache environment you can probably go without the SQL layer. However, you need SQL to expose data to the world (e.g. reporting tools that can only connect through xDBC layer).

In terms for performance you can always make the case that SQL is slower when compared to direct global access, however SQL provides more "readability" for new (and not so new) developers since SQL is "standard" (or wider spread)  compared to ObjectScript.

My rule is to always chose SQL over direct global access unless the performance requirements can only be met by the latter. 

Let's say we must iterate all Persons. Sure, by knowing its storage scheme, we can do it with Order but it's like invoking virtual function by memory offset in c++ world. The only object-oriented (i.e. without knowing too much of object internals) way I know is to use Extent. I wonder why generated class does not provide methods like GetFirst/GetLast/GetNext, this way users of the class could iterate extent in object-oriented way.

 Class User.DQ Extends %SQL.CustomQuery

Property id As %Integer;

Property lastName As %String;

Property firstName As %String;

Property age As %Integer;

Property ptr As %Integer [ Private ];

Property atEnd As %Boolean [ Private ];

Parameter SQLNAME As String = "DQ";

Method %OpenCursor() [ Private ]
try {
// Let's set up some temporary data
set ^CacheTempDQ(1) = $listBuild("Smith","John",42)
set ^CacheTempDQ(2) = $listBuild("Jones","Quincy",80)
set ^CacheTempDQ(3) = $listBuild("Wilson","George",71)
set ^CacheTempDQ(4) = $listBuild("Garcia","Andrew",32)
set ^CacheTempDQ(5) = $listBuild("Smoak","Felicity",24)
set ^CacheTempDQ(6) = $listBuild("Partridge","Audrey",32)
set ^CacheTempDQ(9) = $listBuild("Williams","Andrie",92)
set ^CacheTempDQ(10) = $listBuild("Orr","Robert",62)
set ^CacheTempDQ(11) = $listBuild("Moon","Lila",21)
set ^CacheTempDQ(120) = $listBuild("Lola","Elleoh",67)
set ..ptr = ""
set ..atEnd = 0
set ..%SQLCODE = 0
set ..%Message = ""
catch exception {
set ..%SQLCODE = exception.AsSQLCODE()
set ..%Message = exception.AsSQLMessage()

Method %CloseCursor() [ PlaceAfter = %Next, Private ]
try {
set ..ptr = ""
set ..atEnd = 0
kill ^CacheTempDQ
set ..%SQLCODE = 0
set ..%Message = ""
catch exception {
set ..%SQLCODE = exception.AsSQLCODE()
set ..%Message = exception.AsSQLMessage()

Method %FetchCursor(ByRef sc As %Library.Status = {$$$OK}) As %Library.Integer
try {
if '..atEnd {
set ..ptr = $order(^CacheTempDQ(..ptr),1,data)
if ..ptr {
set link = ""
set ..lastName = $list(data,1)
set ..firstName = $list(data,2)
set ..age = $list(data,3)
else {
set ..atEnd = 1
set ..%SQLCODE = 100
catch exception {
set ..%SQLCODE = exception.AsSQLCODE()
set ..%Message = exception.AsSQLMessage()
return '..atEnd


Then execute this statement:


select * from DQ()




select * from DQ() where lastName %STARTSWITH 'S' order by age DESC


2 row(s) affected


Obviously each project will have different requirements. But here are some generalizations from my experience:

Cache SQL: use when you want to expose your data to report writers who use SQL. Use Cache SQL write capability if you must but may be slower. Fairly easy to read code. But you do NOT need Cache SQL to create an app.

Cache Objects: used most often, especially if you are an object oriented programmer. Hard to not see the advantages of objects in most projects. Reasonable speed and code readability.

Cache Direct Global access: used for legacy programs (lots out there) and may be faster performance (depending on read/write code structure). Difficult to read after years of code changes.

Tom FItzgibbon | | 347-464-8531

There can be more to "speed" that just execution time of your code.  If you need to add an index to help performance, then a Caché SQL query will be able to utilise it without any code changes.  If you are using $Order, then you need to spend some time writing and testing your new code.

I typically use SQL to identify objects, then Object methods to interact with them, but for simple updates of large numbers of objects, I'll usually use a simple SQL Update.  The beauty of Caché is the flexibility to use the best tool for the task at hand

Hi Mike,

In 2013, we did some performance test to figure out the same on Cache 2010 and we found the following:-

Accessing data for reporting etc

      $Order was the fastest, next was  SQL,  objects were the least efficient, probably because it fetches a lot of information every time you open an object reference.  

Insert /Update

     SQL was better as it took care of the Indices and SQL computed fields etc consistently and efficiently.

     Object save, had some inconsistency in terms of SQL computed fields.


     Cache Studio, supported Objects the most :)

I am not sure if this still holds true with the current version.