We have recently observed a few cases in which an SQLI query caused a work process to hang. SQL VARA objects run their queries inside a work process. Such queries occupy the WP 100% while they are running. If something goes wrong with the query — if for example it simply takes a very long time to run — this can lead to other problems. What we have seen is that after a while, a timeout is reached and the PWP gives up hearing back from the hung WP. If this happens to a few WPs, the overall stability and performance of the system can be put in jeopardy.Because of this, we are reevaluating our use of SQL VARA objects. As a part of this, I have devised a way to replace an SQL VARA object with a combination of an EXEC VARA object and an SQL job. This way, the SQL query runs as an ordinary SQL job on an SQL agent. If something goes wrong with the query, it will affect only that job, and not the whole work process.In broad strokes, it works like this:
This approach has several advantages over using SQL VARA objects
I have generalized this approach to make it applicable to many different situations. I will publish example objects soon.
You can also use this approach to replace a BACKEND or FILELIST VARA with a JOBS + EXEC combo. It offers a lot more flexibility, such as the ability to use an agent group or to pick the agent dynamically.
Here is an idea for a way to improve this EXEC VARA wrapper for SQL jobs. The EXEC VARA should automatically cache data retrieved from the remote SQL DB in a local static VARA, and use the cached data instead of querying the remote DB in the following situations:
This would improve the speed & reliability of queries. Obviously it apples to SELECT (read) statements only, and makes sense only for data that are changed infrequently.
I don’t have time to work on this now, but I at least wanted to post a rough description here in case anyone else might want to take a stab at it.
Here’s another advantage of using an EXEC VARA wrapper: