Tags

,

I use SPSiteDataQuery to query around thousands lists  and ten thousands items. when running at web part, it keeps throwing time out. And interesting is the SPSiteDataQuery doesn’t use the indexed columns.

So I use CrossListQueryCache instead.

Below is an example of a query that queries all the Publishing pages libraries that resides in the current SPWeb and all childWebs.

private DataTable ExampleQuery()

        {

            clqi = new CrossListQueryInfo();

 

            // Insert the list types that you want to use. In this case, its the publishing page library (850, see code below)

            clqi.Lists = "<Lists ServerTemplate=\"" + (int)ListServerTemplateCodes.PageLibrary + "\" />";

 

            // Insert the fields that you want to see. If there is a field inside that doesnt exist in the list that you query, your result will be nill, nada, nothing.

            // Make sure that you put in the INTERNAL field names!

            clqi.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"FileLeafRef\" /><FieldRef Name=\"Nieuws_x0020_Type\" /><FieldRef Name=\"Nieuws_x0020_Leverancier\" /><FieldRef Name=\"Uitgelicht\" /><FieldRef Name=\"Created\" /><FieldRef Name=\"Comments\" />";

 

            // scop to use. Another possibility is SiteCollection

            clqi.Webs = "<Webs Scope=\"Recursive\" />";

 

            // turn the cache on

            clqi.UseCache = true;

 

            // if row limit == 0, you will get 0 results

            clqi.RowLimit = 100;

 

            // I know a stringbuilder would be better, but i wanted to show the markup of the query

            clqi.Query = "<OrderBy>" +

                            "<FieldRef Name='Title' />" +

                        "</OrderBy>" +

                        "<Where>" +

                            "<Or>" +

                                "<Eq>" +

                                    "<FieldRef Name='ContentType' />" +

                                    "<Value Type='Text'>News Item</Value>" +

                                "</Eq>" +

                                "<Eq>" +

                                    "<FieldRef Name='ContentType' />" +

                                    "<Value Type='Text'>LocationNews Item</Value>" +

                                "</Eq>" +

                            "</Or>" +

                        "</Where>";

 

            // put the CrossListQueryInfo object into the CrossListQueryCache

            CrossListQueryCache clqc = new CrossListQueryCache(clqi);

 

            // and query the data!

            // make sure: the GetSiteData(SPWeb web) and GetSiteData(SPWeb web, SPSiteDataQuery query) DO NOT use caching!!!

            DataTable tbl = clqc.GetSiteData((SPContext.Current.Site, CrossListQueryCache.ContextUrl());

 

            // return the datatable

            return tbl;

        }

The enum below can be used to make life a little bit easier. I got it from:http://www.aspenhorizons.com/devblog/?p=29

public enum ListServerTemplateCodes
    {

        GenericList = 100,

        DocumentLibrary = 101,

        Survey = 102,

        LinksList = 103,

        AnnouncementsList = 104,

        ContactsList = 105,

        EventsList = 106,

        TasksList = 107,

        DiscussionBoard = 108,

        PictureLibrary = 109,

        DataSources = 110,

        SiteTemplateGallery = 111,

        UserInformationList = 112,

        WebPartGallery = 113,

        ListTemplateGallery = 114,

        XMLFormLibrary = 115,

        MasterPagesGallery = 116,

        NoCodeWorkflows = 117,

        CustomWorkflowProcess = 118,

        WikiPageLibrary = 119,

        CustomGridForAList = 120,

        DataConnectionLibrary = 130,

        WorkflowHistory = 140,

        GanttTasksList = 150,

        MeetingSeriesList = 200,

        MeetingAgendaList = 201,

        MeetingAttendeesList = 202,

        MeetingDecisionsList = 204,

        MeetingObjectivesList = 207,

        MeetingTextBox = 210,

        MeetingThingsToBringList = 211,

        MeetingWorkspacePagesList = 212,

        PortalSitesList = 300,

        BlogPostsList = 301,

        BlogCommentsList = 302,

        BlogCategoriesList = 303,

        PageLibrary = 850,

        IssueTracking = 1100,

        AdministratorTasksList = 1200,

        PersonalDocumentLibrary = 2002,

        PrivateDocumentLibrary = 2003

    }
Advertisements