The SQL Rollup Data Source is NOT compatible with SP 2010. If you would like to upgrade this data source, request to be added as a developer via the People tab

SqlRollupDataSource was developed in order to solve some of the known limitations of SPSiteDataQuery class:
  • Performance with over 1,000 selected list instances produces exception messages and begins to degrade
  • Running queries across a large number of list instances puts significant memory pressure on the server (http://support.microsoft.com/kb/935502)
  • A number of limitations are present with the construction of CAML queries and utilization of certain field types, such as DateTime, Multi-user fields, in the OrderBy and Where parts of a CAML query
  • Some additional rollup metadata (web title, list url, etc) utilized in legacy rollup web parts are not available from the standard result set
  • While the component allows to set a row limit, no true Paging functionality is supported on the backend

SqlRollupDataSource inherits the same properties as CrossSiteDataSource so switching datasources is simply a matter of replacing datasource names.

Assumptions

  • In contrast to what SPSiteDataQuery does, the SQL Rollup shall support rolling up data across one physical kind of a list with the same set of list metadata (list fields) as identified either by ListTemplateID or a list title. In the event of utilizing ListeTemplateID, the use of standard template IDs shall be discouraged, however, will be supported and may lead to inconsistent rollup results. The use of a dedicated ListTemplateID (> 1500) or identification by list title shall be the primary methods. •There shall be an attempt to improve the speed of execution of a query by caching previously retrieved list metadata in memory / persistent storage which may result in intermittent result inconsistency due to un-synchronization of the cached and underlying list metadata resulting from list schema updates. DS1 •While the component shall support Web, List, Folder and Item level permissions, the component shall have a configurable option to ignore Folder and Item Level permissions as a part of its core configurable functionality. The option to disable Item Level permissions shall not be surfaced in the rollup UI in the meantime until a rollup templating approach is introduced to the package.
  • SQL 2005 backend as the minimum supported version
  • All current enhancements including but not limited to exposure of “lookup ids” anddate time field formatting. .
  • Certain extended functionality will be supported in separate dedicated datasource(s)
    • Undefined performance inhibiting post processing behaviors
    • Implied compatibility with aggregate datasource
    • Support for aggregatation of recurring meeting items
  • Security Items not supported in this iteration:
    • Draft Item
    • Lists Read Security Setting

Performance targets / Implementation constraints

The following rollup sources baseline is assumed for the targets described below.
  • A site collection containing 200 sites.
  • Each of the sites provides 5 entries into the rollup return.
  • Each of the entries contains 5 fields of a fixed length of 50 characters

Based on this baseline, the following targets shall be met:
  • Core rollup queries shall be executed by SQL Rollup in no more than 2 distinct SQL calls, not counting any lead up SQL statements executed by WSS runtime. Utilization of the WSS runtime in the lead up to executing rollup queries shall not exceed 3 statements.
  • Overall memory footprint on an executed rollup shall be at most 25% of the memory utilization by SPSiteDataQuery on the same baseline data set
  • Overall execution speed shall be at least 75% upon the first call, and at least 50% upon the subsequent repeat call (with a use of List Schema Cache) as compared to timings of the SPSiteDataQuery.

Sample Configuration

Configuring SqlRollupDataSource is identical to CrossSiteDataSource with just one exception - any fields referenced in CAML query and 'ContentType' field itself must be declared in <Fields> node.

<cc1:SqlRollUpDataSource runat="server" Lists="100" TopLevelSite="." Scope="Web" ContentType="City" FixLookups="False" IncludeListData="False" QueryRowLimit="100" ID="DSC1">
<Fields>
     <FieldRef Name="Title" />
     <FieldRef Name="ContentType" />
     <FieldRef Name="Modified" />
</Fields>
<Query>
</Query>
</cc1:SqlRollUpDataSource>

Last edited Mar 20, 2012 at 5:11 PM by epranav, version 9

Comments

No comments yet.