John Murray · Mar 11, 2016 1m read

Using Class Queries - %SQL.Statement versus %Library.ResultSet

If I have defined a class query in one of my classes and I want to use that query from a method of another class, what are the pros and cons of using the %SQL.Statement interface versus the %Library.ResultSet interface?

I believe %SQL.Statement is the newer interface.

So if the old way is:

USER>s rs=##class(%Library.ResultSet).%New("%Library.File:FileSet")
USER>s sc=rs.Execute("c:\s\","*.txt")
USER>w sc
USER>while rs.%Next() {w !,rs.Data("Name")}


then the new way is:

USER>s oStmt=##class(%SQL.Statement).%New()
USER>s sc=oStmt.%PrepareClassQuery("%Library.File","FileSet")
USER>w sc
USER>s oResult=oStmt.%Execute("c:\s\","*.txt")
USER>while oResult.%Next() {w !,oResult.%Get("Name")}


One drawback of the %SQL.Statement way is that it's harder for code analyzers such as Yuzinji to find the dependency between the calling method and the class query it uses.

Opinions welcome.

0 12 1,155 8


Did you know that when you define a ClassQuery that secretly behind the scenes a ClassMethod is being generated that executes the query ?

For instance if you have a query

Query ByName(Name As %String) As %SQLQuery(CONTAINID = 1)
SELECT %ID,Address,City,Name FROM ClassQuery

There will be a ClassMethod ByNameFunc(Name) which returns a %SQL.StatementResult object.

That's interesting to know. It means that my experiment can become:

USER>s oResult=##class(%Library.File).FileSetFunc("c:\s\","*.txt")
USER>while oResult.%Next() {w !,oResult.%Get("Name")}


And now static code analysis that detects classmethod references can spot the use of the secret FileSetFunc method within the class that implements the query I'm using.

%Library.Result is the old SQL interface and it is recommended to use the new interface %SQL.Statement. %SQL.Statement is capable of providing metadata about the result set and performs better than the old interface in many cases.

The fastest query-like interface is %SQL.CustomResultSet. You need  to write your owl logic, though.

It can be called like this:

Set resultset= ##class(Package.YourCustomQueryRS).%New()
While resultset.%Next() {
   Write resultset.Id,!

Well, that obviously depends on how good your owl logic is. wink  I only recommend this approach if the standard interface is insufficient for a specific need and you have to optimize further. 

Writing your own logic does not leverage optimizations from newer versions and you have to maintain it.

A major benefit of %SQL.CustomResultSet is that you can query any accessible data source, e.g. globals and files.

Yes, but if direct global iteration is used, the speed would probably be higher then sql.

Again, it depends on what you are comparing to. Just looping over a global can be compared to a simple full table scan and the runtime performance should be pretty much the same.

If you have a complex query that can't be boosted by adding just another index or by running tune table, you can probably write your own custom logic. An even better solution is to implement a custom index that can be leveraged by SQL.

You have to keep in mind that the %SQL.CustomResultSet approach does not lead to reusable code. It can be used to solve a very specific problem, but it can't be reused for a similar problem on a different data set.

I think we already have prototype of automatic  "owl logic" analyzer  for COS ;) which can be very useful in many situations )

How about this way, just 

USER>s rs=##class(%File).FileSetFunc("c:\intersystems\")
USER>d rs.%Display()
Name    Type    Size    DateCreated     DateModified    ItemName
C:\InterSystems\Deltanji        D               2016-02-08 18:11:24     2016-02-08 18:21:28     Deltanji
C:\InterSystems\EMS     D               2013-10-22 10:10:17     2014-05-12 11:44:27     EMS
C:\InterSystems\Ensemble10      D               2013-01-12 09:28:38     2015-11-06 12:25:53     Ensemble10
C:\InterSystems\Ensemble14      D               2013-10-22 10:00:14     2015-11-06 12:29:29     Ensemble14

Much simplier, end returns %SQL.ClassQueryResultSet

*Func available since 2012.2

How's your experience with EMS?

UPD. Seen last modified date, nevermind.

It was installed just for test, we can't use it yet.

A couple of small points. %SQL.Statement is the newest and the recommended approach. As was stated previously, in many cases it will perform better. From a pure SQL perspective, a class query is defined as an SQL-Invokable-Routine (SIR). A SIR can be either a procedure or a function. The SQL standard further states that there is an implicit procedure for every function. Functions can be invoked by other SQL statements - SELECT for example, while procedures can be invoked by a CALL statement. The %SQL.Statement %PrepareClassQuery call will prepare a CALL statement for a class query. Of course, the class query must be projected as a SQLPROC (an unfortunate choice of keyword name). 


But, a class query is a function.


There is a new feature (as of 2015.1) called "table-valued function" that allows a class query projected as a SQLPROC to be invoked directly from a SELECT statement in the FROM clause (you can try this in SMP/Explorer/SQL/SAMPLES):

select * from sample.sp_sample_by_name('N')

This option not only allows you to fully encapsulate any hidden (and perhaps unsupported) direct method calls but also allows the statement author to select a subset of available columns, order the results or further restrict the results:


select name,dob from sample.sp_sample_by_name('N')  where DOB > '01/01/1950' order by DOB desc

This feature is not restricted to %SQLQuery class queries, %Query queries are also supported.


As a side note, the hidden and unsupported direct class query method, Func, is what we generate to support the SIR-Function interface.