Response times when using Dynamic and Embedded SQL

When working with InterSystems IRIS, database developers and architects often face a critical decision: whether to use Dynamic SQL or Embedded SQL for querying and updating data. Both methods have their unique strengths and use cases, but understanding their performance implications is essential to making the right choice. Response time, a key metric in evaluating application performance, can vary significantly depending on the SQL approach used. Dynamic SQL offers flexibility, as queries can be constructed and executed at runtime, making it ideal for scenarios with unpredictable or highly variable query needs. Conversely, Embedded SQL emphasizes stability and efficiency by integrating SQL code directly into application logic, offering optimized response times for predefined query patterns.

In this article, I will explore the response times when using these two types of SQL and how they depend on different class structures and usage of parameters. So to do this, I'm going to use the following classes from the diagram:

To test the times, I created the following numbers of objects for each class:

• Patient - 50M
• Visit - 150M
• Doctor - 500K
• Address - 50M

This way, I expected to see some reasonable time for running queries and to see the differences between executing Embedded SQL and Dynamic SQL. The only index that I added was the one-many automatic index for the Doctor-Visit relationship.

So let's look at the queries that I'm going to run, and then at the duration of execution:

1. select count(*) from Hospital.Address
2. select count(*) from Hospital.Address where State = :param
3. select count(*) from Hospital.Patient p left join Hospital.Address a on p.address = a.id
4. select count(*) from Hospital.Patient p left join Hospital.Address a on p.address = a.id where a.State = :param
5. select count(a.Address->State) from Hospital.Patient a
6. select count(*) from Hospital.Patient p where p.Address->State = :param
7. select count(p.Visit->VisitDate) from Hospital.Patient p
8. select count(*) from Hospital.Patient p where p.Visit->VisitDate > :param
9. select count(v.Patient->Name) from Hospital.Visit v
10. select count(*) from Hospital.Visit v where v.Patient->Name %startswith :param
11. select count(v.Patient->Address->State) from Hospital.Visit v
12. select count(*) from Hospital.Visit v where v.Patient->Address->State = :param
13. select count(v.Doctor->Name) from Hospital.Visit v
14. select count(*) from Hospital.Visit v where v.Doctor->Name %startswith :param
15. select count(*) into :p from Hospital.Visit v where v.Doctor->Name %startswith :param and v.Patient->Name %startswith :param
16. select count(*) into :p from Hospital.Visit v where v.Doctor->Name %startswith :param and v.Patient->Name %startswith :param and v.Patient->Address->State = :param1

Obviously, the above is the syntax for Embedded SQL (because there are named parameters). For Dynamic SQL, the queries are almost the same, but instead of named parameters, I have unnamed ones 😉 For example, for the last one, I have the following query:

select count(*) from Hospital.Visit v where v.Doctor->Name %startswith ? and v.Patient->Name %startswith ? and v.Patient->Address->State = ?

Now let's look at the results:

We can see one colossal outlier, which is the first query. The Embedded SQL took a lot more time to execute than Dynamic SQL. Running the same queries several times gave me more or less the same result. So it is what it is. 

In general, we can see that parent-children relationship is a lot slower from the side of the children property, even though all the data of Patient and Visit is stored in the Patient global. The index saved the day for the one-many relationship, and the execution time was considerably shorter. All in all, the response time is mostly similar and differs by less than 10%; sometimes, it is the same. Of course, I used simple queries that didn't take too long to prepare, so this stage could almost be ignored.