Speed of SLB SmartLists Post Upgrade from GP2010 to GP2015
Renee asked 7 years ago
Hello!
Our client recently upgraded from GP2010 to GP2015 R2 (14.00.0952) with SLB 14.00.0223.
For SmartLists created in SLB, they seem to take a lot longer to load in the new environment. For example, in the middle of the day with many users in the system, it took 9 seconds to run a report in GP2010 that took 3 minutes in GP2015. Plus we saw some crazy/scary spikes in CPU on the SQL server. When I ran the same SL after hours, it took 2 minutes and spiked up to 99% of CPU on the SQL server.
In SmartList, it says “Exploring” for a long, long time before the record count starts adding up (1, 2, 3, etc).
Right now, it is hard to tell if it is a weak SQL server or an issue with the software causing the issue.
Are you aware of any issues like this or suggestions on how to effectively troubleshoot and resolve?
Thank you!
Our client recently upgraded from GP2010 to GP2015 R2 (14.00.0952) with SLB 14.00.0223.
For SmartLists created in SLB, they seem to take a lot longer to load in the new environment. For example, in the middle of the day with many users in the system, it took 9 seconds to run a report in GP2010 that took 3 minutes in GP2015. Plus we saw some crazy/scary spikes in CPU on the SQL server. When I ran the same SL after hours, it took 2 minutes and spiked up to 99% of CPU on the SQL server.
In SmartList, it says “Exploring” for a long, long time before the record count starts adding up (1, 2, 3, etc).
Right now, it is hard to tell if it is a weak SQL server or an issue with the software causing the issue.
Are you aware of any issues like this or suggestions on how to effectively troubleshoot and resolve?
Thank you!
Answers
Patrick Roth Staff answered 7 years ago
Renee,
The main question is really:
How much time of the execution (from click a smartlist to starting getting results returned) is from SLB generating the SQL statement vs time of execution.
When you click the smartlist, SmartList itself needs to determine what smartlist is called and then pass control to the product that contains it (SLB in this case). that is negligible.
At that point, SLB then pulls the definition and generates the query. I really doubt that takes more than a few seconds.
Lastly the SQL executes and then finishes and then SLB/SmartList can display the results.
That is going to be the main driver of the time – execution.
So to look into it – you’d want to run a SQL trace on your SQL server. Make sure you select the Duration column. Now run the report in GP and watch the trace.
Now you could see now long the SQL execution of the statement took.
You’re going to find that this is where the bulk of the execution time is going to be.
Now as for “why was 2010 faster vs 2015” – that is going to be a good question and difficult to determine unless you have the old server around to test. But you’d likely need a SQL expert to know more about the “why” of how long that sql statement takes to run and how to make it faster.
As another test then, you can take the SQL from the sql trace and then run in SSMS – the execution time should be the same as from GP.
patrick
The main question is really:
How much time of the execution (from click a smartlist to starting getting results returned) is from SLB generating the SQL statement vs time of execution.
When you click the smartlist, SmartList itself needs to determine what smartlist is called and then pass control to the product that contains it (SLB in this case). that is negligible.
At that point, SLB then pulls the definition and generates the query. I really doubt that takes more than a few seconds.
Lastly the SQL executes and then finishes and then SLB/SmartList can display the results.
That is going to be the main driver of the time – execution.
So to look into it – you’d want to run a SQL trace on your SQL server. Make sure you select the Duration column. Now run the report in GP and watch the trace.
Now you could see now long the SQL execution of the statement took.
You’re going to find that this is where the bulk of the execution time is going to be.
Now as for “why was 2010 faster vs 2015” – that is going to be a good question and difficult to determine unless you have the old server around to test. But you’d likely need a SQL expert to know more about the “why” of how long that sql statement takes to run and how to make it faster.
As another test then, you can take the SQL from the sql trace and then run in SSMS – the execution time should be the same as from GP.
patrick
Renee replied 7 years ago Thank you for the response Patrick. I will look into the items you mentioned.