Go to: Use spquery paging in WSS/Moss

Use spquery paging in WSS/Moss 2007-11-19 11:52:19 Steve peschka in the White Paper: working with large lists in Office Sharepoint Server 2007, this article introduces multiple methods for data access using the WSS/Moss object model. Article The focus is on how to select an appropriate data access method under the large list (large list, that is, the list contains a large number of items, more than 100,000) to improve the performance of data operations.

Most of the tests in this article use the rowlimt attribute to restrict the returned data entries. In essence, the first page or homepage is returned. From the perspective of production or practical application, it is not operable. Of course, the test has basically achieved the goal.

Access to large data volumes is a headache at any time. Generally, only the required data is returned to reduce the time required for Data Reading and network transmission. It is generally solved at the database layer through the stored procedure. Steve peschka does not describe how to paging in the White Paper. Simply put, if we use getitems to return all data to a splistitemcollection or further getable able to a datatable, and then pagination. This only reduces the time between render and the page display time, and does not really reduce the time for accessing the database and the time when data is transmitted from the database to the application server. How can we effectively access data in a large list? One idea is to directly access the database as before. However, this method seems easy and difficult to implement. Because Microsoft has no public database layer design. If permission control is included, a set of data access methods are almost rewritten. The second approach is to use Microsoft's existing object model (OM) for data access. This method uses existing interfaces to access data in an appropriate way.

Spquery is a data access method that can be used in both WSS and moss. At the same time, the performance in this White Paper is also standardized, and real-time data can be obtained, which is a common method for data access. Spquery has three attributes worth noting: viewattributes, rowlimit, and listitemcollectionposition. With viewattributes, you can set the scope of the list to be retrieved and whether the list contains sub-files (default, recursive, recursiveall, and filesonly ). The other two attributes are directly related to pages.

First, introduce the test environment. Install moss2007 on a moss server named mosssvr, have a subsite called news, and create a test list mylist on it. Create subfolder1 and subfolder2 folders in mylist, and directly include three list items titled listitem1, listitem2, and listitem3. Subfolder1 directly contains four list items from listitem11 to listitem14, and subfolder2 directly contains five list items from listitem21 to listitem25. This article mainly introduces the paging method, so it does not need much test data.

Based on the environment, we will first change the section in the wss3.0 SDKCode:

Site =New Spsite("Http: // mosssvr");

Web = site. allwebs ["News"];

List = web. Lists ["Mylist"];

Query =New Spquery();

// Retrieve all projects

Query. viewattributes ="Scope = 'recursiveall '";

Query. viewfields ="<Fieldref name = 'id'/> <fieldref name = 'fsobjtype'/> <fieldref name = 'title'/>";

// Use query and sorting (usually encountered by page)

Query. query ="<Where> <EQ> <fieldref name = 'contenttype'/> <value type = 'text'>Project</Value> </EQ> </where> <orderby> <fieldref name =/"title/" ascending =/"false/"/> </orderby>";

Int I = 1;

Query. rowlimit = (Uint) Ipagesize;

Do

{

SplistitemcollectionListitems = List. getitems (query );

 

Response. Write ("The" + I. tostring () +"Page<Br/>");

// Response. Write (splicp. paginginfo + "<br/> ");

Foreach(SplistitemListitemInListitems)

{

Response. Write (listitem ["ID"]. Tostring () +"."+Response Code. Htmlencode (listitem ["Title"]. Tostring () +"<Br/>");

}

Query. listitemcollectionposition = listitems. listitemcollectionposition;

Try

{

Response. Write (listitems. listitemcollectionposition. paginginfo +"<Br/>");

}

Catch

{

}

I ++;

}

While (Query. listitemcollectionposition! =Null);

// Finalize

Web. Dispose ();

Site. Dispose ();

The output result is as follows:

Page 1
5. listitem3
14. listitem25
13. listitem24
12. listitem23
11. listitem22
Paged = true & p_fsobjtype = 0 & p_title = listitem22 & p_id = 11
Page 1
10. listitem21
4. listitem2
9. listitem14
8. listitem13
7. listitem12
Paged = true & p_fsobjtype = 0 & p_title = listitem12 & p_id = 7
Page 1
6. listitem11
1. listitem1

We can see that this result has been sorted and implemented by page. The paginginfo information is very important. It is a string similar to the querystring construction method. Paged indicates whether to pagination. There are three attributes: p_fsobjtype, p_title, and p_id. It is actually three columns in the list. I tested that these three columns are critical to paging. It is estimated that Microsoft uses this to locate a list item.

However, if we look at the code carefully, we will find that using this code to implement paging is not enough. Because we must first know the previous paginginfo before we can get the content of the next page. In addition, neither the spquery class nor the listitemcollectionposition class has information about the number of rows returned by the entire query. Therefore, the total number of pages or rows cannot be obtained. Therefore, we must work in a different way.

From a statistical perspective, users generally pay more attention to the previous query results, and the chances of viewing the results on the following pages are smaller. Therefore, we only need to return to the previous page first. 20-50 rows are displayed per page, and a maximum of 10 pages are displayed at a time. The returned data volume is less than 5000. This is basically an acceptable range. Therefore, we can first obtain the data of the first 10 pages and then perform paging. To obtain the following pages, you can obtain them when you view more information.

Based on this consideration, we can achieve paging: The first time we get all the data of the specified page number and get the page information of each page, that is, pageinfo. When you browse an existing page, returns the data of the specified page through paging information (the most efficient at this time). When the user does not find the available information on the previous page, obtain all the data of the specified number of pages from the last position and obtain all the page information. So that the user finds the desired information. Page sample code see: http://www.cnblogs.com/dotnba/archive/2007/11/19/964004.html

The disadvantage is that the first page is the most commonly used page, but you need to obtain the data on the next page that may not be needed to obtain the page information on the next page, which may lead to performance degradation. The solution is to reduce the data displayed on each page and the number of pages to be displayed at a time.
From: http://q.blog.sina.com.cn/blogfile.php? Id = 1000267941 & FID = 3f2ef11801000bw1