New post
Question
· Jun 8, 2020

SQL Cached queries

#Deployment #SQL #System Administration #InterSystems IRIS

Since IRIS 2020.1, changed the way how embedded SQL compiled. Now it compiles upon the first execution of such queries.

Is there any way, I could switch that feature back to the previous behavior, or get some more control over it?

We deliver our software as deployed, and we would like to have it well prepared. Another solution would suit us, if we could change the generated classname of the cached query to something which we would be possible to map. At the moment it generates classes %sqlcq.NAMESPACE, but our customers may use different namespaces.

Discussion (16)0
Log in or sign up to continue
Dmitry Maslennikov · Jun 8, 2020

Writing own class queries just restore changes things, which was ok on the previous  versions not an option at all even we would have much less such queries. As We don’t going to use sharping we would better to deliver our application as we did it for Caché.

We need one place where we could change the way.

We also use UnitTests, and as part of our tests we test for performance regression, and now we get some tests failed due to significant performance regression. So now we have to spend time on investigation why it’s happening. I should expect better performance when I migrate between versions, but our automatic tests doesn’t show it.

0 0
Benjamin De Boe · Jun 8, 2020

Can you tie those performance regressions to the new (one-time) embedded SQL late compilation behaviour or is there anything else happening?

As a ground rule, we consider performance regressions upon upgrading to be bugs and will happily work with you to get to the root cause and reconsider optimizer changes and updated heuristics. Please reach out through the WRC if you think we can help.

0 0
Dmitry Maslennikov · Jun 8, 2020

First of all, I have to investigate it by myself, so, I would have something specific to ask through WRC. At the moment, the issue I see now only on cached quires. We build our application, as separate database files with only deployed code, which we deliver to customers. And we run our tests, in the way as it works on the customer's side. The same deployed code, and for sure without cached queries.

0 0
Benjamin De Boe · Jun 8, 2020

You can force embedded SQL to be compiled along with the embedding application code using a compile flag /compileembedded=1. This is not recommended though, so we'd really like to know more about why the current model doesn't suit you. In fact this first-run compiling would allow your customers to leverage the statistics for their data rather than what you baked into it when compiling the embedded SQL. See also this article.

0 0
Dmitry Maslennikov · Jun 8, 2020

I've already found this qualifier, and it actually does not make any difference for us.  Cached query-classes, go to %sqlcq package, where it's mapped, definitely not with our code, and it also contains namespace, which will be different on the customer's side. Another solution would be to just rebuild all cached query, in the application. How it would be possible to do? As I said earlier, we test our queries for performance regressions, and not only queries, everything, and as we don't have any cache at all when we start our tests, our tests now became useless. How we can trust test's results, while the part of performance regression is just a compilation for SQL queries?

So, we have to somehow warm our application.

0 0
Brendan Bannon · Jun 10, 2020

If you want to be sure your customer will get the same generated code you tested in house you could freeze all the plans after running GenerateEmbedded():   Do $SYSTEM.SQL.FreezePlans(1,1,,.Errors)

To generate the Cached queries for your testing and after installing your product on a customer system you can call:  d $SYSTEM.OBJ.CompilePackage("%sqlcq")

These 3 steps should get you back the behaviors you liked about Embedded SQL.

2 0