Performance Issue when running multiple GetListItems over the same list

Mar 14, 2011 at 5:36 PM

I have three lists with a master/detail/detail relationship.  From one master item, I need to provide a list of the detail items, then the detail/detail items.

No problems with the list detail items (around 400 of them).  But when I try to provide the second level of detail/detail (around 4000 items), I'm having significant performance issues. 

At first, I ran separate asynchronous requests, so that I could continuously update the UI.  It works, though slowly (takes around 30 - 45 seconds).  Then I tried using a single request which aggregates all 400 detail requests (where item = detail 1 OR detail 2 OR detail 3 ...OR detail 400).  Total response time is significantly faster, though in my opinion, still not acceptable because I am not updating the UI with progress information while the large query is running.

Then I tried a combination approach, aggregating 50 requests at a time.  The first finishes almost immediately, but then there is a very long delay, then the remaining requests finish immediately.

Wondering if there are resources tied up by the first request?  Or some design issue I'm missing?  For this application, speed is not that important, but a responsive UI is.  So I can get by with using the first approach, but it does bother me that it takes so long.  Any thoughts?

Coordinator
Mar 14, 2011 at 6:05 PM

sjohns:

It's a little hard to know without seeing your script. The one thing which gives me pause is your statement "where item = detail 1 OR detail 2 OR detail 3 ...OR detail 400". I'm wodering what that logic looks like.

If you'd like to post your code, I'd be happy to take a look.

M.

Mar 14, 2011 at 6:55 PM

Code below.  Didn't include method 2, because it's just like Method 3, except without the inner loop which kicks off a request every 50 cases.

All three methods yield the same results, just not as speedily as I'd like.

nbrcases is an array of "|" delimited strings (case# | description)

this may not be the best place to post my question.  I am using SPServices, just not in this section of code because it's a simple GetListItems.  I love SPServices for adding/updating list items, just wrote this section before finding it : ) 

Method 1:

 for (var i=0; i < nbrcases; i++) {
  var wsURL = "mysite/_vti_bin/lists.asmx";
  var splitText = cases[i].split('|');

                var soapEnv2 =
                "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'> \
                    <soap:Body> \
                        <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
                        <listName>{666C5575-C5C7-4A0E-9399-7928FC9E803D}</listName> \
                        <viewName>{1EDD5CA6-1273-41B5-B508-F236A7ED968D}</viewName> \
                        <query><Query><Where><Eq><FieldRef Name='Case_x0020_Guid' /><Value Type='Text'>" + splitText[0] + "</Value></Eq></Where><OrderBy><FieldRef Name='Provider_x0020_Details' /></OrderBy></Query></query> \
                        </GetListItems > \
                        </soap:Body> \
                        </soap:Envelope>";

             // Async web service call.
             $.ajax({
                 url: wsURL,
                 type: "POST",
           async: true,
                 dataType: "xml",
                 data: soapEnv2,
                 error: processAppealError,
                 complete: processAppealComplete,
                 contentType: "text/xml; charset=\"utf-8\""
             });
  }

Method 3:

var bigquery = "<Query><Where>";
var innerQuery = "";
var nbrAjaxRq = 0;

 for (var i=0; i < nbrcases; i++) {
  var splitText = cases[i].split('|');
  innerQuery += "<Eq><FieldRef Name='Case_x0020_Guid' /><Value Type='Text'>" + splitText[0] + "</Value></Eq>";

  // Limit queries to 50 cases
  if (nbrAjaxRq > 0)
      innerQuery = "<Or>" + innerQuery + "</Or>";

  nbrAjaxRq++;
  if (nbrAjaxRq > 50)  {
   bigquery += innerQuery + "</Where><OrderBy><FieldRef Name='Case_x0020_Guid' /></OrderBy></Query>";

   // Same function as in Method 1:
   CaseRequest(bigquery);
   nbrAjaxRq = 0; 
   bigquery = "<Query><Where>";
   innerQuery = "";
   }
  }


...

function CaseRequest(bq){
             var soapEnv2 =
                "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'> \
                    <soap:Body> \
                        <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
                        <listName>{666C5575-C5C7-4A0E-9399-7928FC9E803D}</listName> \
                        <viewName>{893298E4-50D8-4F89-B9EE-B2EB2897FD17}</viewName> \
                        <query>" + bq + "</query> \
                        </GetListItems > \
                        </soap:Body> \
                        </soap:Envelope>";

             // Web service call.
             $.ajax({
                 url: wsURL,
                 type: "POST",
           async: true,
                 dataType: "xml",
                 data: soapEnv2,
                 error: processAppealError,
                 complete: processAppealComplete,
                 contentType: "text/xml; charset=\"utf-8\""
             });
}

Coordinator
Mar 14, 2011 at 7:13 PM
Edited Mar 14, 2011 at 7:52 PM

I think your approach here is probably inefficient, at best. I'd suggest not doing the GetListItems call inside the loop, but rather get all items (or whatever subset makes sense) and then do your filtering on those results. It's going to be far more efficient that way.

M.