New post
Article
· Feb 11 2m read

A look at Dynamic SQL and Embededd SQLContestant

#Contest #SQL #InterSystems IRIS

   

 

 

Unlike the movie mentioned in the image (for those who don't know, Matrix, 1999), the choice between Dynamic SQL and Embedded SQL is not a choice between truth and fantasy, but it is still a decision to be made. Below, I will try to make your choice easier. 

If your need is interactions between the client and the application (and consequently the database), Dynamic SQL may be more appropriate, as it "adapts" very easily to these query changes. However, this dynamism has a cost: with each new query, it is remodeled, which can have a higher cost to execute. Below is a simple example of a Python code snippet. 

Example of Dynamic SQL 

Based solely on the information above, is Embedded SQL the best choice? It depends. Thinking solely and exclusively about agility in execution, we could go for this choice. Since the SQL instructions are inserted directly into the programming code, using HOST variables for data input and output. The goal is not to teach how to use one or another option, but rather to open your mind to the possibilities, learning a little about each one. 

Below, we have some relevant features that should be taken into consideration when starting a development that requires an SQL query: 

As already mentioned, Embedded SQL is often remembered for its performance, but this is not a race and speed is not the only thing we live for. Its integration with several high-level languages ​​allows developers to use the resources in a better way, this is due to the fact that they do not need to search as many external files or separate scripts, making the code cleaner and more maintainable. 

It is also noted for its consistency, after all, changes to the database can be mirrored in the SQL code, thus avoiding possible inconsistencies in the data. And last but not least, the fact that the queries are within the code makes it more secure, since access controls can be implemented directly in the application, thus avoiding unauthorized access and inappropriate queries. 

Well, now we can see what supports Dynamic SQL. This dynamism is easily noticed in its flexibility, that is, everything is shaped as it happens, queries, conditions and even table or field names, thus benefiting the client, the user. It is also marked by ease of administration, since DBAs can perform maintenance on data and databases, checking the impact caused in real time, thus avoiding major compilation problems. 

The summary of all this information, with more theory and little “practice” is that there is no right or wrong side, not even a villain or a good guy, the fact is that knowledge about what will be developed, an in-depth analysis of needs, will lead to a choice... 

Which side of the force will you be on?

 
 
Discussion (11)3
Log in or sign up to continue
Yaron Munz · Feb 12

Some additional notes:

1. If you have many changes (variations) in query structure (e.g. using different column names, or different conditions on the WHERE clause) dynamic SQL is the choice. However, if your queries do not change in structure, only parameters than embedded SQL will be a better choice (e.g. select id for ref.table where name='name')

2. Embedded SQL build the cached query ONCE while you compile, embedded SQL build and compile a cached query every time your SQL structure changes.

2. Speed of dynamic SQL will be identical to embedded SQL (over time) since most of the possible SQL combinations will have cached queries in place - each time you compile a class/table, its "cached queries" are purged (so expect to have slight degradation after releases or changes in class/table)

 3. In case you can use embedded SQL consider giving your client access to a VIEW or a SP (instead of doing SQL against the original table). This way changes you do in class/table will not affect the client

4. As mentioned, security is very important to notice: if you intend to let the client send & execute (dynamically) any SQL, try to limit this for a specific table and sanitize the input to avoid potential "SQL injection" (using s SP with parameter is a good way to secure your backend)

5 0
Enrico Parisi · Feb 12

Embedded SQL build the cached query ONCE while you compile

This is no longer true since some time/version, please check relevant documentation:

Embedded SQL is not compiled when the routine that contains it is compiled. Instead, compilation of Embedded SQL occurs upon the first execution of the SQL code (runtime). First execution defines an executable cached query. This parallels the compilation of Dynamic SQL, where the SQL code is not compiled until the SQL Prepare operation is executed.

As a result, some assumptions, even in the main post, are no longer accurate.

My suggestion is to read the article in this community from @Benjamin De Boe :

New in 2020.1: the Universal Query Cache

2 0
Dan Pasco · 17 hr ago

@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
2 0