July 21, 2010 at 10:32 am
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 🙂
July 21, 2010 at 10:44 am
If the application is using a cursor to query the database, then you would need to modify the app.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply