New post
Question
· Jun 12, 2017

Is Caché SQL essential to Caché Objects?

#Caché #SQL

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?

Or

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
Warlin Garcia · Jun 12, 2017

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. 

0 0
Arto Alatalo · Jun 13, 2017

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.

0 0
Dan Pasco · Jun 28, 2017 
 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()

 

or 

 

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

 
 
idlastNamefirstNameage
 SmithJohn42
 SmoakFelicity24

2 row(s) affected

 

1 0
Tom Fitzgibbon · Jun 12, 2017

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 | gototomAtG...l.com | 347-464-8531

0 0
Chris Stewart · Jun 12, 2017

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

2 0
Anil Mathew · Jun 13, 2017

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.

Coding

     Cache Studio, supported Objects the most :)

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

Anil

0 0