July 31, 2019 at 8:50 pm
Hi all.
Developers have a rich User Interface (UI) and it is loading very slowly, about 20-30 sec. They have a grid, several drop-downs . They don't call any stored procedure. Instead, they are executing several queries to populate these controls, and they call them prior to page is actually displayed. Totally, they have 5 queries to pre-populate these controls, and they sent all of them to me. I tuned these queries, added all needed indexes, but in one case query returns ~ 27k recs to pre-populate drop-down list. Even with index, it takes 3 sec. All these queries run in SSMS for about 3-8 sec all together.
What I can suggest to developers? Can they run just one query to load the page, and after that, "behind the scenes", to populate all the rest controls asynchronously?
Another question: can I suggest them to have a log of all calls with all timings? Because I suspect that most of the time is spent on their side, not SQL Server. Can they write such a log to a file on App. server side? They claim that such a log will slow the process even more.
What is general practice in such situations? They are using C# and LINQ.
Thanks
July 31, 2019 at 9:48 pm
To check elapsed timer the developers can implement a timer variable - stop timer, stop timer and then get elapsed time - example https://docs.microsoft.com/en-us/dotnet/api/system.timers.timer?view=netframework-4.8
But timer may not help here depending on how they have their forms and linq calls implemented.
would it be possible to post just the sample code for the drop down for those 27k records to see if I can suggest another aproach.
this should include both the linq code to retrieve the data and the population of the drop down
July 31, 2019 at 10:21 pm
I don't have application code. What developers sent me is a SQL query "converted" from LINQ. And it's very simple, basically select from a single table with a couple of filters in Where clause; I've build an index on them and it by itself is running fine. My problem is despite of few seconds in SSMS, on actual web-page it takes much longer.
July 31, 2019 at 10:51 pm
My problem is despite of few seconds in SSMS, on actual web-page it takes much longer.
then it can be different execution plans, if there are no locks
are the queries parametrised?
also, worth to check :
http://www.sommarskog.se/query-plan-mysteries.html
August 1, 2019 at 11:21 am
Time to employ extended events and capture the query metrics. That's the single best thing you can do. Identify how long each query takes, how frequently they are called, and collectively which ones are causing the most load. Then, you do two things, simultaneously. First, you work with the developers to identify methods that may be causing problems (for example, they call a query to fill a drop down, but that data never changes, can they cache it at the app server level instead? Then, instead of 10k calls an hour you get one call a day, that sort of thing). Second, you look at the queries that are causing the most load. 95% of the time or more, generated queries from LINQ or Entity Framework are well-behaved corporate citizens. However, 5% of them can be horrific. So, identify that 5% and determine the cause. It can be bad code on the LINQ side (happens all the time), or, it may be a situation where you need a stored procedure because LINQ is going to generate bad code no matter what. This may entail looking at the queries themselves, performance metrics and execution plans. This is the fun part of the job.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 2, 2019 at 8:15 pm
Thank you, Grant, for good advises. Because of permission issues, I am using Profiler (of course in lower environment) instead of extended events. Am I missing any features?
August 2, 2019 at 8:54 pm
If someone is loading 27k rows into a dropdown, it can work for search/typing, but the render time might be high.
August 2, 2019 at 9:48 pm
Trace can do pretty much all you're looking for. It's just going to put a much heavier load on the system while it collects the data. Also, it's horrific at filtering what it captures. You don't need to worry about that just yet since you really need to capture everything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply