SQL

SQL Reference
SQL Reference is an enhanced summary and description of the SQL Commands and Functions. In the InterSystems documentation, the SQL Reference page lists all Caché SQL Commands and Functions in a single alphabetical list. Caché Wiki supplements that list by also sorting them into functional categories. Those categories are: Data Definition Language (DDL), Data Manipulation Language (DML) and Functions. The following paragraphs contain links to a full alphabetical list and to summaries by category. They are listed on separate pages so that you can bookmark them separately.

Full Reference
This page is similar to the InterSystems summary, but with the addition of some additional information about the category and (for functions) styles of each entry.

DDL Reference
DDL is used to create, amend and delete database tables and views, and to maintain such things as indexes and constraints. It includes such commands as CREATE, DROP, AMEND and GRANT.

DML Reference
DML is used to manipulate the data in tables and views. The four basic actions supported by DML are SELECT, UPDATE, INSERT and DELETE. This category contains all the commands for construction the ‘framework’ of a query, with no transformation of values. It also includes Aggregate (Grouping) functions, such as SUM, COUNT, etc.

Functions Reference
Functions are used in queries to transform values. Most take a single argument (usually a fieldname) and return a transformed value. There are some with multiple arguments. Typical functions inlude FLOOR, LCASE, SUBSTRING.

Tuning

 * Make sure that you have allocated as much memory for global buffers as possible on your system, which will speed up the creation of temp tables for SQL commands
 * Ensure that you have run TuneTable on all of your tables within Caché so that Selectivity and ExtentSize are properly set. See http://docs.intersystems.com/cache20081/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_optimizing
 * Take a close look at your data model, and identify which indices you should create and build based on the types of queries which are being run
 * Take a few key token SQL queries, and tune them within the Caché SMP. This would involve running the SQL statement within the SMP, and looking at the Query Plan that is generated.  Based on that plan, you should decide if it would make sense to add a new Index, etc (i.e. if the plan is walking your entire 2.5M row table as part of the plan, an Index would be a good idea :)

Embedded SQL
SQL can be embedded in Cache Object Script like this:

&sql(select * from ...)

Encapsulation
Embedded SQL is not well encapsulated. The following variables are created or used by embedded SQL and may overwrite your own variables of the same name:


 * SQLCODE - indicates the return code for an SQL statement
 * %ROWCOUNT - indicates the number of rows returned by a query
 * %ROWID - id of row returned
 * %MSG - user define message in trigger code
 * %OK - trigger code abort flag
 * %0* - a whole host of variables for each named cursor (undocumented)
 * %msg - sometimes created, sometimes killed (undocumented)
 * %objlasterror - status string containing last object error if there has been one (undocumented)
 * %0CacheRowLimit - seems to control the number of rows returned by a query (undocumented)

See also: 2007.1 Documentation about variables used by embedded SQL.

Comparison of %ResultSet and Embedded SQL
Commentary by Mark Sires about the difference between using %ResultSet and embedded SQL:

I don't profess to fully understand what goes on under the covers, but I have generally found %ResultSet to give better (more reliable) results than &sql these days. In your particular situation, I think the reason is that &sql with a cursor is making a call over the ODBC driver each time you do a fetch, and the cursor based fetch wasn't working over ODBC. Using %ResultSet with a dynamic query in this case makes a single call over ODBC and retrieves all 100 rows.

There may be cases where %ResultSet doesn't retrieve the entire query in a single call, but that seems to be its 'preferred' behavior, so in cases like yours, where the return set is small, and the cost of each call is high, it works better.

&SQL with a cursor seems to 'prefer' to walk through the table directly, rather than pulling the matching row id's into a temporary storage area. In some cases this is good, in other cases, not so much. If it hasn't chosen a good index to use in traversing the table (do not use -> in your where clause, it will always choose poorly), each 'Fetch' can be slow. If the data changes while it is 'walking', it can cause unexpected behavior. And if you use another &SQL statement to update the same table while in the cursor loop, very unexpected behavior can sometimes occur. On a much older version, I tracked that problem down to the reuse of some variable names in the generated code if the same table was used in 2 &SQL - they may have fixed that in subsequent releases. I changed my techniques to always use the cursor to retrieve the rows to look/modify, and then do any updates after closing the cursor, so I haven't worried about whether it has been changed or not. This is also why %ResultSet frequently works better for me, the generated code basically does what I was doing to retrieve the matching rows into a temporary storage area.

Another technique I use to avoid some of the runtime penalties (which really aren't much) of %Resultset is to put frequently used Static (or simple variable substitution) queries in a class, then use that query instead of %DynamicQuery. Using your query as an example, this could be the query class:

Class MyStuff.queries [ Abstract ] { Query GPSData(StartValue As %BigInt = 0, StopValue As %BigInt = 100) As %SQLQuery(CONTAINID = 1) {  select ID, Location from SatNav.GpsAddress where ID>:StartValue and ID<:StopValue } } Then to use it : Set query=##class(%ResultSet).%New("MyStuff.queries") s ok=query.Execute(0,100) The rest is pretty much the same.

If you are using the same query over and over again, this is more efficient because the query is 'precompiled', and the code lives in the routines generated by the class compilation, not in every routine it is used in. Also, if you need to tweak the query, you have one place to fix it. Of course, the downside, if you tweak it, you can break all the places that use it.

This commentary was originally posted here.

Projection of Lists as Sub-tables
Projecting a list as sub-table has always been possible but was a bit tricky, with recent versions (at least from 5.2, maybe before) it's easier:

Property MyList As list Of %String(STORAGEDEFAULT = "array");

One caveat: if you modify a class where storage has already been generated for the list property, adding the parameter does not recreate the storage definition where this behavior is implemented.

This commentary was originally posted here.

DISPLAYLIST/VALUELIST
Use %External to display the Display List value instead of the Value List value in your SQL.

SELECT %External(FIELDNAME) FROM SAMPLES

SQL Shell/SQL from terminal
You can execute SQL statements from the terminal. This is useful when using a GUI SQL tool, because no database connection or BDE need be set up in windows.

D $System.SQL.Shell >> SELECT * FROM MyTable (1)>> go

Note that you can paste a multiple line SQL statement into terminal.