Dynamic SQL doesn't have to build the query solution every time you run the same statement. Once you initially prepare an SQL Statement, the implementation of that statement is cached and reused. The only overhead should be limited to a hopefully very brief cache resolve. There are ways that you can write a statement that makes the hashing algorithm we use very inefficient (prepare:execute ratio p:n, the larger 'n' is, the better - 1:1 being the worst). Since we now use a unified model, even embedded SQL will produce a cached implementation. Embedded reduces the cache resolve time.

@Andre Larsen Barbosa - I am the author of the intiial dynamic SQL 'feature'. I no longer work in this area and many significant enhancements have been made since my initial work. That said, I applaud your article. It is quite nice.

There is one feature that not many people know about - there is an Object mode that you can use with Dynamic Statement. Object mode is interesting when your query selects column values whose underlying type is a IRIS ObjectScript Class that is an object type (Persistent, Serial, Stream). Here is a trivial example of how it might be used.

LATEST:USER>set statement = ##class(%SQL.Statement).%New()

LATEST:USER>set statement.%ObjectSelectMode = 1

LATEST:USER>do statement.prepare("select name,address from person")

LATEST:USER>set result = statement.execute()

LATEST:USER>write result.%Next()
1
LATEST:USER>write result.name
po1
LATEST:USER>write result.address
7@User.Address
LATEST:USER>write result.address.city
Boston
LATEST:USER>write result.address.state
MA

    set stream = ##class(%Stream.FileCharacter).%OpenId("/Users/.../data/continents-en.json")

Did you know that you can just open a file as a stream?

And for iterating - this has been in the product for a while now. This is the code for my :pp alias.

ClassMethod pp(set As %AbstractSet, offset As %Integer = 0)
{
#define QUOTE(%val) $zu(144,1,%val)
    try {
        set isLabeled = set."_isLabeled"()
        if (isLabeled) {
            write "{"
            set close = "}"
        } else {
            write "["
            set close = "]"
        }
        set it = set.iterator()
        while it.hasNext() {
            set next = it.next()
            if $isobject(next.value) {
                write !,?(offset+2)
                write:isLabeled $$$QUOTE(next.key),": "
                do ..pp(next.value, offset + 2)
            } else {
                write !,?(offset+2),$select(isLabeled:$$$QUOTE(next.key)_": ",1:""),$$$QUOTE(next.value)
            }
            if it.hasNext() {
                write ","
            }
        }
        write !,?offset,close
    } catch exc {
        write !,"Exception caught: ",exc.AsSQLMessage()
    }
    return
}

This is in %ASQ.SetUtils. My alias, pp - pretty print, is this:

pp        do ##class(%ASQ.SetUtils).pp($*)