Query on SharePoint List Items


In many cases you find yourself writing yet another report against a SharePoint list.

 while you know and preach the Microsft guidelines of not accessing the Content database directly , I find that performing a CAML query and paging through it is very slow and not efficient at all. 

I have researched this ove and over and looks that you can get better performance if you directly connect to the SQL Content database directly with only one condition ( Please please please , use the the NO LOCK option on all the SELECT statements) 

as if you don’t it will slow down you Content DB a bit ( not noticable at run time ) but will be noticable if you have large Lists. 

General concept on this solution is INNER JOIN of your AllUserData Table on the UserInfo Data table . 

You can also use the views but I am not that good to know how to use them yet. 

 In General here is the Sample code.

USE YOUR_DATABASE_NAME

Go

Select AllUserData.[tp_ID] AS Id

,UserInfo.tp_SiteID

,UserInfo.tp_ID

,UserInfo.tp_Title

,UserInfo.tp_Email

,UserInfo.tp_Login

,[tp_RowOrdinal]

,[tp_Version]

,[tp_Author]

,[tp_Editor]

,[tp_Modified]

,[tp_Created]

,[nvarchar1] As Title

,[nvarchar2] As Author

,[nvarchar4] AS AI_Type

,[nvarchar5] As Functional_Area

,[nvarchar6] As Assigned_By

,[nvarchar7] As Priority

,[ntext1]

,[ntext2] As Disposition

,[sql_variant1]

,[int1] AS INT01

,[int2] AS INT02

,isnull([float1],0) As FL01

,isnull([float2],0) As FL02

,isnull([float3],0) As FL03

,isnull([float4],0) As FL04

,isnull([float5],0) As FL05

,[datetime1] As DT01

,[datetime2] As DT02

,[datetime3] As DT03

,[datetime4] As DT04

,[datetime5] As DT05

,[bit1]

,[bit2]

FROM AllUserData WITH (NOLOCK)

INNER JOIN

UserInfo WITH (NOLOCK)

ON

(AllUserData.tp_SiteId = UserInfo.tp_SiteID

AND

AllUserData.tp_Author = UserInfo.tp_ID)

WHERE

/*Place your List Guid here instead of my sample */

AllUserData.tp_ListId=’fb070c65-6642-40f5-abf7-f2baa2f67a30′

Order By AlluserData.tp_ID

This dataset will be similar to the All Items View and you should be able to do all sort of Reporting on the dataset as with any SSRS report.

Thank you and Good luck.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s