SPService Fetch data problem

Jan 30, 2013 at 6:01 AM

 Hi I am using spservice to fetch data from sharepoint list. My User Skills list have thousands of record. But my query returns only 10-12 records.For fetching 10 records it takes 15-20 second time to fetch data. It is very slow. What can i do to increase performance. I have 7-8 column in list . And i have also done indexing on necessary column.Plz help

  var querySkill = "<Query><Where><And><Eq><FieldRef Name='User_x003a_ID' /><Value Type='Lookup'>"+rowUserId+"</Value></Eq><And><Eq><FieldRef Name='Role_x003a_ID' /><Value Type='Lookup'>"+rowRoleId+"</Value></Eq><And><Eq><FieldRef Name='Skill_x0020_Category_x003a_ID' /><Value Type='Lookup'>"+rowCategoryId+"</Value></Eq><Eq><FieldRef Name='Skill_x0020_Group_x003a_ID' /><Value Type='Lookup'>"+rowGroupId+"</Value></Eq></And></And></And></Where></Query>"
        var camlViewFieldsSkill = "<ViewFields><FieldRef Name='Fulfillment' /></ViewFields>";
        var groupPer;
        var groupArray = new Array();
        $().SPServices({
            operation: "GetListItems",
            async: false,
            listName: "User Skills",
            CAMLQuery: querySkill,
            CAMLViewFields: camlViewFieldsSkill,
            completefunc: function (xData, Status) {
                $(xData.responseXML).find("z\\:row, row").each(function () {
                  var skillId = $(this).attr("ows_ID");
                  var skillName=$(this).attr("ows_Skill").split("#")[1];
                  var fulfilment=$(this).attr("ows_Fulfillment");
                    $(subGridSkill).jqGrid('addRowData', skillId, { id: skillId, skillid: skillId, skill:skillName, skillfulfillment: fulfilment });

                });

            }
        });


Coordinator
Jan 30, 2013 at 7:34 PM

shraddhashah:

Generally speaking, the Web Services are far faster than the UI, so your performance problem sounds like it is either the farm itself or maybe the versions of the jQuery librarires you are using.

Given that you have .find("z\\:row, row") in your code - you should be using SPFilterNode - you must be using a jQuery version less than 1.7 and probably an SPServices version less than 0.7.0. Upgrade to the newest versions and let me know if you still see the performance problems.

M.

Jan 30, 2013 at 8:16 PM
Edited Jan 30, 2013 at 8:23 PM
<p>As a point of reference, I run GetListItems operations (with a CAMLQuery option) on a list that has more than 12,000 records quite regularly.&nbsp; The query returns a set of 1-15 records on average and can do so in about 1-2 seconds.&nbsp; I'm only filtering on one field, however.&nbsp; I noticed your syntax for the CAMLQuery looks odd.&nbsp; It doesn't appear you're nesting your &lt;And&gt; tags properly.&nbsp; When you break yours out, it appears like...</p> <div style="background-color:white; color:black"> <div style="color:black; background-color:white"> <div style="color:black; background-color:white"> <div style="color:black; background-color:white"> <pre>&lt;Query&gt; &lt;Where&gt; &lt;And&gt; &lt;Eq&gt;&lt;FieldRef Name=<span style="color:#a31515">'User_x003a_ID'</span> /&gt;&lt;Value Type=<span style="color:#a31515">'Lookup'</span>&gt;<span style="color:#a31515">&quot;&#43;rowUserId&#43;&quot;</span>&lt;/Value&gt;&lt;/Eq&gt; &lt;And&gt; &lt;Eq&gt;&lt;FieldRef Name=<span style="color:#a31515">'Role_x003a_ID'</span> /&gt;&lt;Value Type=<span style="color:#a31515">'Lookup'</span>&gt;<span style="color:#a31515">&quot;&#43;rowRoleId&#43;&quot;</span>&lt;/Value&gt;&lt;/Eq&gt; &lt;And&gt; &lt;Eq&gt;&lt;FieldRef Name=<span style="color:#a31515">'Skill_x0020_Category_x003a_ID'</span> /&gt;&lt;Value Type=<span style="color:#a31515">'Lookup'</span>&gt;<span style="color:#a31515">&quot;&#43;rowCategoryId&#43;&quot;</span>&lt;/Value&gt;&lt;/Eq&gt; &lt;Eq&gt;&lt;FieldRef Name=<span style="color:#a31515">'Skill_x0020_Group_x003a_ID'</span> /&gt;&lt;Value Type=<span style="color:#a31515">'Lookup'</span>&gt;<span style="color:#a31515">&quot;&#43;rowGroupId&#43;&quot;</span>&lt;/Value&gt;&lt;/Eq&gt; &lt;/And&gt; &lt;/And&gt; &lt;/And&gt; &lt;/Where&gt; &lt;/Query&gt; </pre> </div> </div> </div> </div> <div style="background-color:white; color:black">Try re-constructing your CAML to look like the example below and see if it makes a difference</div> <p>&#65279;</p> <div style="color:black; background-color:white"> <pre>&lt;Query&gt; &lt;Where&gt; &lt;And&gt; &lt;And&gt; &lt;Eq&gt;&lt;FieldRef Name=<span style="color:#a31515">'User_x003a_ID'</span> /&gt;&lt;Value Type=<span style="color:#a31515">'Lookup'</span>&gt;<span style="color:#a31515">&quot;&#43;rowUserId&#43;&quot;</span>&lt;/Value&gt;&lt;/Eq&gt; &lt;Eq&gt;&lt;FieldRef Name=<span style="color:#a31515">'Role_x003a_ID'</span> /&gt;&lt;Value Type=<span style="color:#a31515">'Lookup'</span>&gt;<span style="color:#a31515">&quot;&#43;rowRoleId&#43;&quot;</span>&lt;/Value&gt;&lt;/Eq&gt; &lt;/And&gt; &lt;And&gt; &lt;Eq&gt;&lt;FieldRef Name=<span style="color:#a31515">'Skill_x0020_Category_x003a_ID'</span> /&gt;&lt;Value Type=<span style="color:#a31515">'Lookup'</span>&gt;<span style="color:#a31515">&quot;&#43;rowCategoryId&#43;&quot;</span>&lt;/Value&gt;&lt;/Eq&gt; &lt;Eq&gt;&lt;FieldRef Name=<span style="color:#a31515">'Skill_x0020_Group_x003a_ID'</span> /&gt;&lt;Value Type=<span style="color:#a31515">'Lookup'</span>&gt;<span style="color:#a31515">&quot;&#43;rowGroupId&#43;&quot;</span>&lt;/Value&gt;&lt;/Eq&gt; &lt;/And&gt; &lt;/And&gt; &lt;/Where&gt; &lt;/Query&gt; </pre> </div> <p>&nbsp;</p> <p>This way, your conditions are all grouped in pairs, nests of pairs.</p> <p>Geoff</p>
Jan 31, 2013 at 4:55 AM
Hi gkoliver,
  Can u plz again post your code. It mash up with html code...Thanks
Regards
shraddha
Jan 31, 2013 at 10:02 AM
Hi gkoliver,
             I have change query as u describe.But no effect.
Regards,
shraddha
Jan 31, 2013 at 10:06 AM
hi sympmarc,
      I have use SPFilterNode. Firstly it takes 15-17 seconds to fetch records. Now it takes 12 seconds to fetch data. Still not acceptable. I have read some where Client object model is using restful webservie to fetch data and performance is better than spservice. Is is like???Can u provide some guidance...
Thanks
shraddha
Jan 31, 2013 at 12:40 PM
@shraddhashah
Have you tried removing this line from your code?
$(subGridSkill).jqGrid('addRowData', skillId, { id: skillId, skillid: skillId, skill:skillName, skillfulfillment: fulfilment })
Test the code without using that other library and see what happens.

Cheers,
Matthew
Jan 31, 2013 at 1:23 PM
Whoa, that was weird. It looked fine yesterday after I posted it....at any rate, here's how my reply loooked originally....

As a point of reference, I run GetListItems operations (with a CAMLQuery option) on a list that has more than 12,000 records quite regularly. The query returns a set of 1-15 records on average and can do so in about 1-2 seconds.  I'm only filtering on one field, however, I noticed your syntax for the CAMLQuery looks odd. It doesn't appear you're nesting your <And> tags properly. When you break yours out, it appears like..

<Query>
<Where>
    <And>
        <Eq><FieldRef Name='User_x003a_ID' /><Value Type='Lookup'>"+rowUserId+"</Value></Eq>
        <And>
            <Eq><FieldRef Name='Role_x003a_ID' /><Value Type='Lookup'>"+rowRoleId+"</Value></Eq>
            <And>
                <Eq><FieldRef Name='Skill_x0020_Category_x003a_ID' /><Value Type='Lookup'>"+rowCategoryId+"</Value></Eq>
                <Eq><FieldRef Name='Skill_x0020_Group_x003a_ID' /><Value Type='Lookup'>"+rowGroupId+"</Value></Eq>
            </And>
        </And>
    </And>
</Where>
</Query>

Try re-constructing your CAML to look like the example below and see if it makes a difference...

<Query>
<Where>
    <And>
        <And>
            <Eq><FieldRef Name='User_x003a_ID' /><Value Type='Lookup'>"+rowUserId+"</Value></Eq>
            <Eq><FieldRef Name='Role_x003a_ID' /><Value Type='Lookup'>"+rowRoleId+"</Value></Eq>
        </And>
        <And>
            <Eq><FieldRef Name='Skill_x0020_Category_x003a_ID' /><Value Type='Lookup'>"+rowCategoryId+"</Value></Eq>
            <Eq><FieldRef Name='Skill_x0020_Group_x003a_ID' /><Value Type='Lookup'>"+rowGroupId+"</Value></Eq>
        </And>
    </And>
</Where>
</Query>

This way, your conditions are all grouped in pairs, nests of pairs.
Coordinator
Jan 31, 2013 at 1:24 PM
What version of jQuery and SPServices are you using?

M.
Coordinator
Jan 31, 2013 at 1:25 PM
Geoff:

They switched to markdown sometime in the last few days, which I'm not liking at all. Some things got messed up.

M.
Jan 31, 2013 at 2:09 PM
sympmarc wrote:
Geoff:

They switched to markdown sometime in the last few days, which I'm not liking at all. Some things got messed up.

M.
Hmmm, I see that. Even my updated reply ignored the indents (tab characters) after I saved it....gotta love feature enhancements! ;-)

At any rate, it appears shraddhashah tried my suggestion already and didn't experience any change so appareantly the CAML will work the same in either form(?). In my production environment, I'm running jQuery 1.7.2 and SPServices 0.7.1a.
Coordinator
Jan 31, 2013 at 2:12 PM
I was wondering what versions shraddha is running.

Geoff: Yeah, a mess. You should upgrade to SPServices 0.7.2. Significant performance improvements.

M.
Jan 31, 2013 at 7:06 PM
I did upgrade my development site to 0.7.2 with jQuery 1.8.3. However, based on an issue with the UpdateListItems operation that I experienced (http://spservices.codeplex.com/workitem/10112), I made a slight modification to the 0.7.2 source code. It's working great with that little tweak. I don't want to run that in my production environment just yet. I plan to wait for 0.7.3.
Feb 1, 2013 at 5:47 AM
Hi sympmarc,
               Thanks for replay. I am using jquery.SPServices-0.7.2.js and jquery-1.6.4.min.js version.  I have near around 3 lake data in list. But my query returns only 15 records. To fetch 15 records it takes 10-12 seconds. Additionally if i run my query in caml viewer it take 1 seconds to fetch data.
Regards,
shraddha
Coordinator
Feb 1, 2013 at 11:51 AM
Did you try Matt's suggestion to see where the delay is? You can also see how much time each request is taking in Fiddler or Firebug.

I'd also suggest upgrading to a recent version of jQuery.

M.
Feb 1, 2013 at 11:56 AM
I am using jqgrid to display data. With jquery 1.7 version it is not working in ie 8. Thats why i am using jquery 1.6 version. I have also debug in firebug. It takes time to fetch data. It takes 10 second to come in complete function.
Coordinator
Feb 1, 2013 at 1:14 PM
Well, I'm not sure where the delay is, then. Are you sure that the 10 seconds is in the call to GetListItems, excluding the jqgrid manipulation?

M.
Feb 1, 2013 at 1:45 PM
I'd love to see some metrics. Since you have Firebug, can you run this slightly modified code?
  var startTime = +new Date();
  
  var querySkill = "<Query><Where><And><Eq><FieldRef Name='User_x003a_ID' /><Value Type='Lookup'>"+rowUserId+"</Value></Eq><And><Eq><FieldRef Name='Role_x003a_ID' /><Value Type='Lookup'>"+rowRoleId+"</Value></Eq><And><Eq><FieldRef Name='Skill_x0020_Category_x003a_ID' /><Value Type='Lookup'>"+rowCategoryId+"</Value></Eq><Eq><FieldRef Name='Skill_x0020_Group_x003a_ID' /><Value Type='Lookup'>"+rowGroupId+"</Value></Eq></And></And></And></Where></Query>"
        var camlViewFieldsSkill = "<ViewFields><FieldRef Name='Fulfillment' /></ViewFields>";
        var groupPer;
        var groupArray = new Array();
        $().SPServices({
            operation: "GetListItems",
            async: false,
            listName: "User Skills",
            CAMLQuery: querySkill,
            CAMLViewFields: camlViewFieldsSkill,
            completefunc: function (xData, Status) {
                console.log( "Milliseconds it takes to get the XML back from the server: " + +new Date() - startTime );
                
                $(xData.responseXML).find("z\\:row, row").each(function () {
                  var skillId = $(this).attr("ows_ID");
                  var skillName=$(this).attr("ows_Skill").split("#")[1];
                  var fulfilment=$(this).attr("ows_Fulfillment");
                    $(subGridSkill).jqGrid('addRowData', skillId, { id: skillId, skillid: skillId, skill:skillName, skillfulfillment: fulfilment });

                });
                
                console.log( "Milliseconds it takes to process and display the XML: " + +new Date() - startTime );
            }
        });
Just post both of the messages that are displayed in the console.

Cheers,
Matthew
Coordinator
Feb 1, 2013 at 1:47 PM
Edited Feb 1, 2013 at 1:47 PM
Matt:

Tsk, tsk.
.find("z\\:row, row")
M.
Feb 1, 2013 at 2:58 PM
Hahaha... Totally didn't see that till now. ;-)

.SPFilterNode FTW!
Feb 2, 2013 at 5:27 AM
Edited Feb 2, 2013 at 5:29 AM
I have just devide by 1000 to get second.

console.log("Milliseconds it takes to get the XML back from the server: " + (new Date() - startTime) / 1000);
console.log("Milliseconds it takes to process and display the XML: " + (new Date() - startTime) / 1000);

It dispslay below result

Milliseconds it takes to get the XML back from the server: 9.58
Milliseconds it takes to process and display the XML: 9.592
Coordinator
Feb 4, 2013 at 3:42 AM
I don't know what else to suggest, really.

Try a simpler query on the same list. Try retrieving from other lists and see if the performace is also slow. Try removing the indices (I've never seen a benefit), etc. The GetListItems call probably isn't the problem.

M.
Feb 4, 2013 at 4:41 AM
With other list it is working fine but when their is 4 lakh data in list than it takes time. With large list it have problem
Coordinator
Feb 4, 2013 at 1:37 PM
It may be time to reconsider your architecture in some way, then. I'm out of ideas. It's the SharePoint server that is introducing the performance lags.

M.
May 5, 2014 at 8:55 AM
Edited May 5, 2014 at 9:00 AM
I am facing problem to fetch data. There are 50 rows that need to be fetched, but only 30 has been populated. Please help me.
//The Web Service method we are calling, to read list items we use 'GetListItems'
        var method = "GetListItems";
        
        //The display name of the list we are reading data from
        var list = "Content Metadata Draft";
        //define fields to retrive from list
       
        var fieldsToRead =     "<ViewFields>" 
                                "<FieldRef Name='Metadata_x0020_Name' />" 
                                "<FieldRef Name='Value' />" 
                                 "<FieldRef Name='Editor' />"
                                    "</ViewFields>";
      //CAML query to retrive values from Content Metadata list
                 
        var query = "<Query>" 
                        "<Where>"                        
                             "<Eq>"
                                 "<FieldRef Name='Content_x0020_Name' LookupId='True' />"
                                " <Value Type='Lookup'>"+intContentId+"</Value>"
                                "</Eq> "
                        "</Where>" 
                        "<OrderBy>"  
                            "<FieldRef Name='Metadata_x0020_Name'/>" 
                        "</OrderBy>"                         
                    "</Query>";
        var queryOption= " <QueryOptions><RowLimit>200</RowLimit></QueryOptions>" ;

        //Here is our SPServices Call where we pass in the variables that we have set above
        $().SPServices({
                operation: method,
                async: false,  
                listName: list,
                CAMLViewFields: fieldsToRead,
                  CAMLQuery: query,   
                  CAMLQueryOptions:queryOption,
                    CAMLRowLimit: 200,
                  
                    completefunc: function (xData, Status) {
                    
                        //var   itemCount = parseFloat($(xData.responseXML).find("rs\\:data").attr("ItemCount"));
                        
                        //alert("itemCount "+itemCount );
                        
                     //this code iterates through every row of data returned from the web service call
                        $(xData.responseXML).SPFilterNode("z:row").each(function() { 
                                                 
                            var strMetadataName = ($(this).attr("ows_Metadata_x0020_Name"));  
                                                                        
                            var strMetadataValue = ($(this).attr("ows_Value"));
                            
                            var strEditor = ($(this).attr("ows_Editor")).split(";#")[1];                         
                                                                            
                            
                        });                
                    }
        });