Apex Code Development You need to sign in to do that
Don't have an account?
salesforceit Multiple users getting a query_timeout error when logging into our app
Hello,
In the past week or so we have been experiencing problems with multiple users of our app getting a error :
In the past week or so we have been experiencing problems with multiple users of our app getting a error :
onErrorSfdc: {"readyState":4,"responseText":"[{\"message\":\"Your query request was running for too long.\",\"errorCode\":\"QUERY_TIMEOUT\"}]","status":400,"statusText":"Bad Request"}
The wierd thing is that we have not done anything differently app wise in the last three years, our app has not undergone any updates or upgrades and the phones they are run on are the same type of phones they have always run on. None of our login procedures have changed, and our number of app users has not increased in over 10 months. Initially there is a large percentage of our users telling us the data is unavailable, then after some time they are finally able to pull the data they need down. Is there something going on with our database that is making the search go on for too long? did some limit change, do we need to revisit our code, or do something regarding our database on your side?
May be your data in Salesforce would have increased in this 3 years of time and that is the reason you are getting this error.
three years ago we were working with 30GB storage, at 90 - 95 % capacity ( at its highest that is 28.5 GB at the lowest that is 27 GB. We added 2 GB just in case, we are currently at 84% capacity that is 26.88 GB. This change is less than what it was at three years ago when it was at it lowest then, and every thing worked fine.
The real issue we are having is that this has been a recent thing (within the last two weeks or so). It has also seemed to occur most obviously on sundays and mondays - this is when we get the most error messages, towards the middle of the week it is not as much of a problem, generally our cloud is running a little faster then...
If we can get off of the size of our data since it has actually decreased over the course of the three years that the app has been around. There has to be another issue. The indexing might be an option but I do not think that this will likely speed up the query enough to make the difference on those two "magical" days. Also the size of the data did not change significantly at all in the last two weeks, and as stated before, the mobile app worked fine, For instance, there was no visible, gradual, slowing until we had this issue which is what a growing database would cause. This was an abrupt change in reliability and service that was not there three weeks ago and suddenly there now. So I guess I could rephrase: is there something happening at the server level on Salesforce's side or did some rules change recently that caused this to come about? If so does anyone have documentation?
Another contributing factor can be that the index you are using can get fragmented as data in your org gets deleted. Salesforce can "coalesce" the index, which is equivalent to defragging your hard disk. This is uncommon since the index is self-maintaining, but it does happen, especially when a lot of data is deleted.
Your best bet is to file a case with Salesforce Support and give them the SOQL query that is performing badly. They have a tool that can analyze the query and determine which index gets used and how much time the elements of the query take to calculate and cross-reference. Even if you don't know which query is taking a lot of time, there's a log that reports which queries are slow every hour, and they can see if your org is on the list.
This is a fairly common issue for orgs that have been around for a while. They need a tune-up occasionally.
There is a tool called "Long Running Request" and another tool called "Explain SFDC Query Plan". By picking up a few of the long running queries and running them through the query plan analyzer, the Support engineer should be able to determine which fields are impacting your query performance. It takes a bit of talent and experience to find the right things to focus on, especially with very complex queries.
There's also a tool called "RecommendedIndexes" which is automatically populated based on an automated analysis of recent queries in your org. The Support engineer can evaluate whether those recommendations would be likely to improve your query performance. There are currently only two entries listed there.
I have narrowed down the query that is causing issues in our mobile app there is an "or" statement in it; we have optimised it to run without the "or" statement. I had to do some testing in off hours to verify. On saturday between 530AM EST and 830AM EST I was running tests on workbench and the query that i am referring to would timeout referrencing a header issue. Outside of these early morning hours it would run but take 16 -19 seconds to run. When I optimised the query and deployed the change it seemed to pull down for me in a test environment. The odd thing is I ran the query this morning when we were not sending information to salesforce and that query timed out again. Meaning that we were not the cause of the delay.
Here's what I think is happening, and tell me if it makes sense. After the winter 17 update some sort of server mainteneance runs early morning( maybe a backup?), or if we have a shared server someone else on the server is bogarding resources, so when we go to do our querys they time out and fail. If either of these is the case what do you think the fix should be? I can include the query in this doc if you think it would help, but I am not sure about the security of doing so.
There is a bit of a spike in DB CPU usage on your instance in the early EST hours every day, but even at peak we still have about 50% of capacity to spare. We monitor both DB load and HTTP response times very closely, and we can throttle a user or org if they are consuming more than their fair share. While it's possible for one org to impact others for a brief period, there's enough redundancy built in that it's unlikely to cause any noticeable impact for one user or org.
There are a lot of ways to investigate performance issues. It's easier to look at a particular request and DB query and see what was going on during the 20 seconds that the query ran, rathan than trying to look at everything that was being processed and trying to see if there was some combination of issues that caused a problem. Normally if there's a system-wide degredation it will stand out on the dash boards we monitor, and I didn't see anything unusual on your instance this morning.
You may be right, but the issue remains something is causing a query that works at other times of the day to time out. This something continues to happen until approximately 730 - 830 AM EST I have forwarded my ticket holder the specific query that failed on saturday. I have not spoken to him for at least 4 days, but I have left him messages on the ticket. The query failing at this time is a symptom. It did not fail before, the query did not change for over three years in fact i had to dig to find the query that was failing because no one knew where it was. The problem seems to be some sort of slowness that has recently occured, that runs just as we are running the app. Or more accurately as our trucks are trying to run the app. We deliver a lot and have over 50 of them.
I did run it today as well at 550 AM and got the same response on workbench, UNKNOWN ERROR : Error fetching HTTP Headers.
My improved query which reduces the time from 16-19 sec to .117 sec failed to load in my app this morning. It is not the queries, Something is happening on our server that is blocking us from being able to run queries.
I will try again after the "slow time" and It will have no problem running. I run the same exact query. and it is for data that is at least five days old now.
Update, @ 620 my improved query suceeded. The Original query failed with the unknown error: error fetching http headers. PLEASE note that they both would have succedded before oct 15.
The fact that the query timed out over the weekend on Saturday suggests to me that this doesn't have anything to do with database load or other tenant activities.
My suspicion is that the table you are querying has grown over time, and the timing of this issue happens to be coincidental. Can you add an ORDERBY and LIMIT to your query? or maybe restrict the results to only rows that have been modified in the past day/week/month? If your business logic allows for that, it will give your users a better experience regardless of what other factors are in play here.