Hey all,
Thanks for looking.
I have encountered a number of performance issues with our ERP system, whereby it uses sp_cursoropen , fetch and close to send all of it's queries to the SQL database.
Basically I have run a series of performance stests and benchmarks versus simply running the same query from QA or via sp_executesql.
The differences are quite staggering.
One recent example;
via QA:
Reads: 6
Processing Time: 30 ms
via ERP cursor:
Reads: 2.3 million
Processing time: 28 minutes
This is to return the same single result.
Unfortunately, I am not able to change the code in the program (although if there is a simple way of managing it, it's easily justifiable - Dynamics AX/Axapta).
Is there a way of managing the way queries are handled once they leave the ERP system in this format?
I.e is there any way of forcing the query to be managed in SQL?
Has anyone else encountered a similar issue?
Any other suggestions?
I have already optimised Index settings and put in a cluster where there wasn't one (which was again unfortunately all too often...).
Any help or tips massively appreciated 🙂