Apex Code Development You need to sign in to do that
Don't have an account?
icemft1976 Initial load time too long - query structure issue?
Hi,
I have a page that queries a list of accounts using a run time query ( i.e database.query(runtimestring); ) , this query contains a few subqueries as well as the LIKE operator on a text field. It is calling up a list of accounts depending on the Client Services advisor Id passed in as well as any number of possible Company Division abbreviations (the page is part of a larger app that lets client services view their client book and filter acording to products their clients buy).
No matter if the page loads with a query that returns 2 results or 3000 (based on the passed in URL parameters) it takes upwards of 1 minute to load the first time its visited. From there on out, the load time is about 5 seconds.
The Developer Console timeline puts the delay squarely on the DB query. I'm wondering if there is a situation where the system is generating a sort of "view" on the iniital query construction at run time...and then its using the optimized view on subsequent queries? I'm not sure i can rewrite the logic to make it a compiled query but I'm willing to try.
Again, regardless of the number of records returned or the post prcoessing that needs to happen based onthe returned records count and details.I'm looking for insight on known issues that might be contributing to this behavior.
I've seen a few people with similar issues but I'd like to sytstematically test this to identify the culprit. I'm as sure I can be its not an asset or Js or rights issue.
Thanks for your time.
AJ
EDIT: Ful disclaimner: the page I'm working on is being fetched by jquery ajax call and the returned HTML is being inserted into a different main "dashboard" page. But I can recreate the DB delay issue by going directly to the page in question.
The result set may be small, but the trick will be how many records had to be read to get to the result set (finding 1 record in 1 million with 'a' as the middle letter of the Name field will take a long time). Finding an indexed field to help the query engine narrow down potential matching records will make a huge difference. This will turn the query into a 'selective query' and be much more responsive. This may be useful: http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm
Thanks, Jeff.
Good information to remember, I'll run some tests and see if I can isolate the query structure as the issue. I assume this could explain the behavior I was seeing provided the subsequent queries were taking advantage of the initial query's "waking up" of the data. Otherwise I'd be expecting every subsequent query to exhibit the same 30-60 second load time (and they don't).
Side note: Have you ever had any performance issues with dynamic versus compiled querying? I assume its a bit of a muddy situation since anytime I need to resort to dynamic queries it means I'm more than likely doing A) more broad queries or B) queries including 'like' or some other operator on non-indexed content. need more testing!
Thanks for the note. I will post back my findings.
AJ
I've never compared the timing that closely. Generally, if query time has been an issue, I've worked on ways to speed it up. I tend to use the dynamic vs compiled queries interchangeably based on the scenario I'm dealing with.
Let us know what you find.