+16
What can be the reason that the initial connection takes seconds… I have another query that runs 5 ms direct on DB.. and this time I see that it stays for seconds in initial connection.
and after 6.7 minutes it's done..
Best answer by Freddy
View original
+23
Hi Freddy,
Could it be that the web server Indicium is running has a CPU% of like 99% or 100%? This could cause Indicium to not have the resources to respond.
Maybe an Indicium restart can help?
Hello Freddy,
It is strange that it shows Initial connection at first, but when it’s done it shows the 6.7 minutes were spent on the query, which makes me think it’s another SQL Server parameter sniffing issue that would be solved by the recompile query option, just like your other topic. So you could try that first.
If it is actually the initial connection that takes long, then it’s more likely to be some kind of network issue such as a poor connection, congested line, slow firewall, overworked web server or slow DNS look up. I really doubt that it’s actually this though because the Query measurement is performed inside of Indicium, meaning that the 6.7 minutes started after the request arrived at Indicium.
If the recompile didn’t work, then more information would help in troubleshooting this issue. Does it happen consistently or is there some kind of pattern to it? E.g. is it always the first request after logging in? Is it always the same table/view?
+16
I think I found the issue, completely overlooked it. I have a key value that I can search. Apparently it's passed on via the U-GUI as 'contains' in stead of a 'Equal to' (see other post).. in this case the key value is passed as a like '%value%' and then it scans the entire database 3 times.. because this view has 40 Mln+ companies, 60 Mln. Establishments and 30 Mln partners … when I do the query with EQ it's milliseconds because it actually does a seek. But when it's passed with a 'LIKE'.. the actual query plan shows the 3 streams (company, establishment and socio) being scanned ( so it scans to all records and this takes a long time).
The other problem that added to the 6 minutes is the way that the filter form fills itself. I have a related field with 60 options.. however it doesn't get the options from the related table but it first joins it with the full view and than groups it to make it unique.. This takes a lot of time, also because the lookup is a left join and not a full join. (see also other post )
After removing this form filter field.. I just have the issue that EQ to is not passed on, it always shows contains. And of course I have the force remcompile off at the moment as it breaks all the secondary queries such as count and the population of the form filter fields.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
