Good day everyone, I am trying to figure out if ai have a potential concurrency issue, or some other problem with a stored procedure that i recently ported from SQL Server
QueryBanding: Not enable
I have a stored procedure that takes input from an application, or service, that does a look up and with recursion builds the employee hierarchy. I use a volatile table to create the hierarchy, and then a global temporary table to insert the results of the CTE and using a dynamic cursor display the results back to the calling application or service. When I execute stored proc, it runs with in one to five seconds (there are six to twelve queries). When I look at the dbql I consistently see that the queries all execute within less than a second, but the call step is always the longest step no matter what. I’m assuming that is because of a recompile, but not sure. Does anyone know why? (this is a SQL stored procedure)
When my performance testers run their automated set of tests they state they are finding a large number of failures, however when I review the dbql for errors I see less than one percent. We don't have query banding set up, so it makes me wonder if we could potentially be seeing a locking issue? I had always assumed that the volatile tables were session specific, and thus shouldn’t be in conflict with one another. and should be able to avoid any type of locking issue or concurrency issues. Any feedback would be great
The CALL step is always the longest because it's the overall runtime, it sums up the runtimes of each SQL within the SP.
In QryLogV you get the CALL step with a RequestNum x, the SQLs within the SP show the same RequestNum x and an increasing InternalRequestNum.
Volatile tables should not cause any locks, similar for Temp tables (there's only lock when they get materialized within the session).
"large number of failures" - which failures? Didn't they provide you with more details? There's ErrorCode and ErrorText in QryLogV.
You might also be able to speed it up when you run multiple SQLs within the SP in parallel, can you show the source code?