fbpx

Benefits of Dynamic SQL Queries

Static SQL queries are powerful, but when you start to do things at scale, you need to leverage the benefits of dynamic SQL queries. In this post, we will discuss the core concepts of dynamic SQL and some of the ways that you can use it.

Core Concepts

What is dynamic SQL? In essence, it’s SQL that is modified on the fly, immediately prior to execution. Dynamic SQL consists of a few different pieces.

Templates: A template is an SQL query string that contains named parameters within it.  Parameters can be enclosed in double braces, for example: {{ x }}.  The syntax for denoting parameters in dynamic SQL varies according to how the SQL is written.  For this post, we are using templated SQL syntax.  Templates give you flexibility in your SQL queries.  Below is an example of a static SQL query versus a templated SQL query.

Static SQL:

SELECT COUNT(*) FROM users WHERE created = NOW()-7;

Templated SQL:

SELECT COUNT(*) FROM users WHERE {{ mycolumn }} >= NOW()-7;

Parameters: Parameters are the named variables that you pass into a template. In the Templated SQL example above, mycolumn is a parameter. The column name in the SQL query is being dynamically set based on the value of the parameter.

Pointers: A pointer is a specific type of parameter. When processing ‘big data’, you need to tackle it in batches. A pointer holds a datetime or integer value that records the end of the last batch that was successfully processed. If something breaks during your data processing, or if it just needs to be paused for a while, you can use a pointer to determine where to begin again so that you don’t have to repeat any processing that you have already completed.

Use Cases and Benefits

There are two common use cases where dynamic SQL is heavily leveraged. The first use case is batch processing data. As mentioned above, when the amount of data is too large to be handled all at once, you need to split your data into batches for processing. Static SQL queries aren’t effective for batch processing, so dynamic SQL is needed. You use a template and a pointer to move through your data, batch by batch.

For example, you have a table with data about users and you need to update a piece of information for the new users added to it. In this scenario, you want to process users in batches based on their creation date. Each batch is defined by a range start date and a range end date and contains all the users created between those dates.

UPDATE users
     INNER JOIN user_type on user_type.id = users.type_id
SET user_budget = user_type.budget_range
WHERE users.created > ‘{{ range_start_date }}’ and users.created <= ‘{{ range_end_date }}’;

Alternatively, you could create a long script with repeated statements differentiated by static date range values, but that involves a lot of repeated effort and is prone to error.

The second use case involves using distributed schemas. For example, you have data that is structured similarly in different schemas in different databases. In this scenario, you would use a query like this.

SELECT * FROM {{ myschema }}.orders;

Using this template allows you to query data from multiple tables containing information about orders across different schemas using a single query.

The two use cases can also occur together when you have a large amount of similarly structured data stored in many tables with different schemas that you want to process in batches. While it makes sense to combine your schema parameter with batch range pointers, we recommend using a different set of pointers for each table.

Making it Even Easier

Our free SQL Runner engine allows you to easily implement all of the concepts discussed above.  Your dynamic SQL query can be stored in a Shared Object and be scheduled to run on several different databases.  Each run can be individualized with values set in Range and User Defined Parameters groups.  The free Nominode platform will automatically use and update your range pointer values to effortlessly manage batch processing and fault recovery.  This reduces the complexity of your data management and hardens your processes against errors and unforeseen interruption.

Byte sized updates delivered to your inbox
  • This field is for validation purposes and should be left unchanged.