none
SPListItemCollection and GetDataTable method

    Question

  • Hi,

    We have a list with 800 items and contains 25 fields in it.

    I need to retrieve the list data in DataTable format. The SPQuery returns 640 Items in SPListItemCollection, for which time taken is in milliseconds. But when I try to convert this collection into DataTable using GetDataTable method, it is taking 3~4 minutes!

    Her is the code snippet I am using:

    public DataTable GetListData(SPWeb web, string listName, bool includeSubFolders, string camlQuery, string viewFields)
    {	    
    	    DataTable dtListItems = null;
    
                try
                {
                    web.Lists.IncludeRootFolder = true;
                    SPList list = GetList(web, listName);
    
                    SPQuery spquery = new SPQuery();
                    spquery.ViewFields = viewFields;
                    spquery.RowLimit = 100000000;
                    spquery.Query = camlQuery;
                     
                    if (includeSubFolders)
                    {
                        //Retrieve all items under list sub-folder
                        spquery.ViewAttributes = "Scope='Recursive'";
                    }
    
                    SPListItemCollection items = list.GetItems(spquery);
                    
                    dtListItems = items.GetDataTable();
                }
                catch (Exception ex)
                {
                    //LOG ExCEPTION
                }
    
                return dtListItems;
    }

    Is there anything wrong in my code?

    Are there anyways for me to convert SPListItemCollection data into DataTable other than using GetDataTable?

    Thanks

    Arun

    Sunday, August 02, 2009 10:10 AM

Answers

  • Hi Arun,

    How did you meassure the time for the GetItem()? I assume it is only the time of the call itself, without any iteration on the items / field value processing. The later ones do require the most of the time.

    What SP level does your environment run with?

    What field types you have in your list? Do you have custom / lookup / users and groups fields? It would be better to have an override for GetDataTable() where one could specify columns that are not needed in the datatable, so fields that require more times and really not needed could be skipped, but the ViewFields of the SPQuery can be used for that too. 

    What application needs all the 800 items and 25 fields in a data table? How do you display it and how users can use it? Is it not possible to apply some sort of paging (for example, 20 items per page)?

    I suggest you to play with the number of items and fields, start with a low number of items, and only a few fields, measure the time, then add new fields, and measure again. Then increase the item number measure with few fields and more fields (would be better to measure adding field by field). Then you have data to create computing time graphs as the function of item count / fields. You can see what item number / field type is critical for you.

    You can also try to create your custom SPListItemCollection -> DataTable creation method that may produce better performance in your special case.

    Have you checked this thread (for example, using SPSiteDataQuery as alternative)?
    Why is first access to SPListItemCollection object is so slow?
    http://social.msdn.microsoft.com/forums/en-US/sharepointdevelopment/thread/979e2564-8007-463e-9709-af2ef93f4f72

    Also may be useful (see Manipulating Data)
    SharePoint List Performance
    http://blog.solanite.com/keith/Lists/Posts/Post.aspx?ID=15

    A possible workaround might be to call the GetListItems method of the Lists web service, and use the returning XML as the source of the DataTable. It requires some pre-processing (see Columns missing when using Sharepoint Webservices, http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment/thread/95855246-b8f8-4dda-897a-c4480cc74044), and may be not optimal for your item and field counts.

    Peter
    Sunday, August 02, 2009 11:49 AM

All replies

  • Hi Arun,

    How did you meassure the time for the GetItem()? I assume it is only the time of the call itself, without any iteration on the items / field value processing. The later ones do require the most of the time.

    What SP level does your environment run with?

    What field types you have in your list? Do you have custom / lookup / users and groups fields? It would be better to have an override for GetDataTable() where one could specify columns that are not needed in the datatable, so fields that require more times and really not needed could be skipped, but the ViewFields of the SPQuery can be used for that too. 

    What application needs all the 800 items and 25 fields in a data table? How do you display it and how users can use it? Is it not possible to apply some sort of paging (for example, 20 items per page)?

    I suggest you to play with the number of items and fields, start with a low number of items, and only a few fields, measure the time, then add new fields, and measure again. Then increase the item number measure with few fields and more fields (would be better to measure adding field by field). Then you have data to create computing time graphs as the function of item count / fields. You can see what item number / field type is critical for you.

    You can also try to create your custom SPListItemCollection -> DataTable creation method that may produce better performance in your special case.

    Have you checked this thread (for example, using SPSiteDataQuery as alternative)?
    Why is first access to SPListItemCollection object is so slow?
    http://social.msdn.microsoft.com/forums/en-US/sharepointdevelopment/thread/979e2564-8007-463e-9709-af2ef93f4f72

    Also may be useful (see Manipulating Data)
    SharePoint List Performance
    http://blog.solanite.com/keith/Lists/Posts/Post.aspx?ID=15

    A possible workaround might be to call the GetListItems method of the Lists web service, and use the returning XML as the source of the DataTable. It requires some pre-processing (see Columns missing when using Sharepoint Webservices, http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment/thread/95855246-b8f8-4dda-897a-c4480cc74044), and may be not optimal for your item and field counts.

    Peter
    Sunday, August 02, 2009 11:49 AM
  • Hi Peter,

    Sorry could not reply back to your solution.

    Infact your suggestions helped me to resolve the issues.

    As you pointed out, I had 6 Lookup columns (multi-value). Because of these columns, the data conversion (from SPListItemCollection to DataTable) was taking more time. Since, I was using these columns for displaying the data on the custom user controls, I added 6 more additional columns of SingleLineText, and wrote event handlers to copy the value from Lookup columns into Text columns. And then in the query, I reffered Text columns instead of Lookup Columns. Now the time taken was ~50 ms!!!

    Thank you very much for guiding me in right direction.

    Best Regards
    Arun
    Friday, September 11, 2009 11:12 AM
  • Hi Arun,

    Thank you very much for your kind feedback. I'm happy I was able to help to solve your issue.

    Peter
    Friday, September 11, 2009 12:30 PM