New post
Article
· Mar 11, 2016 1m read

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

#Caché #SQL

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
1
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
1
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.

Discussion (12)2
Log in or sign up to continue
Herman Slagman · Mar 11, 2016

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
 WHERE (Name %STARTSWITH :Name)
}

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

1 0
John Murray · Mar 11, 2016

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.

2 0
Stefan Wittmann · Mar 11, 2016

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.

2 0
Stefan Wittmann · Mar 11, 2016

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.

0 0
Dmitry Maslennikov · Mar 11, 2016

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

2 0
Dan Pasco · Mar 14, 2016

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.

2 0