SQL Cached queries
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.
Comments
I think not that this is possible.
The reason was to make it as flexible as any other Dynamic query.
I'm convinced this was necessary to support Sharding.
But you have the option to write your own custom class query
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.
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.
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.
The universal query cache has very little if anything to do with sharding (see here)
I also wouldn't recommend using custom class queries for anything for anything you can achieve with SQL. It leaves you with much more flexibility and transparent benefits (presumed ;-) ) as you update your table statistics or upgrade to a new version.
Calling @Benjamin De Boe.
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.
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.
In 2020.2 we added $system.OBJ.GenerateEmbedded function which allows all universal cached queries to be compiled on a customer system even if all the routines/classes are deployed. So this function can be run after installation to prepare the SQL queries before you bring the system up.
That's good, I should definitely test it. But 2020.2 means that it will arrive only in 2021.1 MR?
It looks like it was added in 2020.3, not 2020.2
We're looking to port this to the next maintenance release for 2020.1 as well, so that'd be 2020.1.1
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.
That's very useful to know!
Can you clarify how often should this be run - Just once? Or on every boot? Or on every application upgrade where queries may have changed/been added?
Cached queries are kept until they are explicitly purged or you upgrade your IRIS instance. So it is only useful to run this after an upgrade of IRIS or after you explicitly purged your cached queries.
If you load a new version of your classes, or compile your classes on the system that will also purge Cached Queries so you would want to run the method then as well.