+ Start a Discussion
tinman44tinman44 

Long query times?

I am wondering if anyone knows if the size of an organization's SFDC org would effect query times? I am building an s-control in Javascript with the new Ajax 3.3 toolkit and it is taking it anywhere from 2-7 seconds to query the Opportunity object for one record. My other queries fly but any on the Opportunity object go slow. Here is an example my query (Very simple only getting 4 fields and I know the opp id):
 
---
 var opp_id = Id of the opportunity I am working with
 var qr = "Select Fld1__c, Fld2__c, Fld3__c, Fld4__c from Opportunity where Id = '" + opp_id + "'";
 var queryResult = sforceClient.query(qr);
---
 
Any ideas/thoughts/advice on slow queries would be greatly appreciated. Thanks in advance.
 

Message Edited by tinman44 on 07-06-2006 03:37 PM

CRMfusion - GWCRMfusion - GW
Yes, the size of an organization will effect the time to execute the query.  We have some clients that this is an serious issue with.  A workaround is to limit the data returned based on an indexed field (such as recordTypeId).

Since you know the ID of the object you are accessing could you not use a retrieve call? (I'm not familure with the AJAX toolkit)

GlennW
tinman44tinman44

Thanks for the response. I was unfamiliar with the retrive call. I will check it out and see if it speeds up my script.

Thanks.

tinman44tinman44
I changed my query to a retrieve call and it is still taking anywhere from 2-12 seconds to query the opportunity table for 1 record.
 
There are roughly 80,000 opportunities in this table. I am wondering if anyone else has seen this long of a delay using the API or if and SFDC developers have any ideas for me? Wondering if building this s-control in PHP woudl gain me any speed on my queries?
 
Any other input is greatly appreciated. Thanks.
 
 
SuperfellSuperfell
Please log a case with support.


SteveBowerSteveBower
Two thoughts (both very questionable  :-)   )

1. Are you measuring just the first attempt at a Retrieve from an opportunity, or does this happen for second, third, and subsequent Retrieve attempts?

I'm wondering if it's somehow related to the describe call on Opportunity that has to be done first unless it's already been cached.   Is it possible that with other actions you're doing in some sort of loop the cache is being invalidated somehow, and it's having to re-describe for each call as well as process the query?


2. I don't know if this would help, but it's easy to try and might.  (Perhaps someone else could chime in and let me know if this is a stupid thought).  Would it help to drop the value for Batchsize down to 1 with sforceClient.setBatchSize(1);   Perhaps requesting a smaller buffer, etc.  I can't think it would help much, if at all.  And, certainly not enough to make up for the lousy performance you're experiencing.  I'm just curious.




SuperfellSuperfell
It might seem counter intuitive, but smaller batch sizes == worse perf. (we need to create the cursor, more round trips to get your data, etc).
tinman44tinman44
Steve...I think you have nailed it in point 1.
 
This is my first call to the opportunity object and this company I am working wiht has roughly 399 fields on the opportunity.

So...is there any way to do the describe object some time before outside this s-control  and store it somewhere to retrieve in ths s-control? This would avoid that first long query on the object.
 
Thanks for the direction...and any more advice/direction

Message Edited by tinman44 on 07-13-2006 11:29 AM

SteveBowerSteveBower
Hi, well, I don't think there is any way to cache the describe results in a previous *page*, however you can certainly try to do it before any user activity takes place.  Of course if you don't have user input, etc. and this needs to happen right away, you're kind of stuck. 

But, you can do the describe yourself, early, and throw away the results.  Define a function

function doNothing(queryResult) { return;}

and then issue a describe call on Opportunity specifying that function to process the results.  Do this first thing into your s-control and at least the cache will get loaded before other stuff happens.


Upon thinking about it, there might be a way that once an object has been described you store the sforceClient.js object cache in a local cookie and then restore it when you enter a new s-control from the cookie (if it's there).   So, you'd bypass the describe calls on subsequent s-control usages, but you'd still have to load/store it into the cookie.  I suppose that would be faster, but A) I don't know if it would FIT for a huge object, B) it would require some heavy mucking about with the sforceClient hash structures, and C) there might be all sorts of coherency issues to deal with.

So probably best not to go down that path unless it's "really important".

Steve Bower.

tinman44tinman44

That did the trick! Thanks for the advice.

I added a describe object call to the first line of code and had is process async...so the the rest of the code loaded while it was being built.

Worked like a charm. Thanks again for the direction.

SteveBowerSteveBower
Glad I could help.

Now, I'm *tempted* to ask why there are 399 fields in an opportunity record.  But, since I'm afraid you might actually tell me, I won't!  :-)

Steve Bower.

tinman44tinman44
We are asking the same quesiton. Let's just say that it is a large client who has many lines of business...and have worked with other consulting companies that did not see the downside of soooo many fields.
niranjanniranjan
Hi Steve,
 
I am facing the same problem what you have mentioned at first point.
 
I am developing my application in Java. My process is taking long query times for subsequent Retrieve attempts.
 
can you please suggest me what to do.
 
Regards,
Niranjan.
cheenathcheenath
Good that you got it working.

It seems you are using the beta toolkit, please take a
look at the production toolkit [1]. Production toolkit does not
need a describe call before doing query.

-cheenath

[1] https://wiki.apexdevnet.com/index.php/API#AJAX




niranjanniranjan

Is AJAX is related to Java Script? I am developing in Java.

This process retrieved 40,000 records from AccountTeamMember table and stored in teamMemberDto (i.e. in local memory).

Then the following query given below should get executed for 40,000 times on AccountShare table
 queryResult = sfdcClient.query( sql +"'"+ teamMemberDTO.getAccountId() +"'"+
          " and UserOrGroupId = "+"'"+teamMemberDTO.getUserId() +"'");
       
But I observed the from the execution of the process is that it is taking very long time for the subsequent records.
 
i.e, it took - 3 hours  -  for first 4500 records
for next    - 3 hours  -  it processed only 1500 records.
for next    - 3 hours  -  it processed only 500 records
 
Any suggestions?
 
Awaiting for your reply.

SuperfellSuperfell
Have you profiled your app, that sounds like its slowing down because its holding too much in memory and is actually spending all its time doing memory management.