Searches paged in the database have posed a problem in SQL Server at least prior to version 2005. I’ve found some solution to the problem on the net but they were so cludgy that I would never really put anything like that in a production server. I hope the following will shed some light on how they work in general by using in in EPiServer context.
Theoretically in EPiServer you can pull the pages that match the criteria from the database with EPiServer.Global.EPDataFactory.FindPagesWithCriteria() into the PageList but that seemed to be imposing a strong performance penalty with increased number of pages meeting the criteria. Since this search is sometimes done even multiple times on a page request in our project we needed something better.
1CREATE Procedure PagedSearch
2 @Condition varchar(1024),
3 @PropertyName varchar(1024),
4 @PropertyType varchar(1024),
5 @PageSize int,
6 @PageNumber int,
7 @Offset int
8AS
9BEGIN
10 DECLARE @RowStart int
11 DECLARE @RowEnd int
12
13 SET @RowStart = @PageSize * @PageNumber + @Offset;
14 SET @RowEnd = @RowStart + @PageSize + @Offset;
15
16 WITH PageRefs AS
17 (SELECT page.pkID as PageId,
18 ROW_NUMBER() OVER (ORDER BY page.StartPublish DESC) as RowNumber
19 FROM tblPage page, tblProperty propValue, tblPageDefinition propDef
20 WHERE page.pkID = propValue.fkPageID
21 AND propValue.fkPageDefinitionID = propDef.pkID
22 AND propDef.fkPageDefinitionTypeID = @propertyType -- is proper type
23 AND propValue.Searchable -- the property is searchable
24 AND propValue.String like @Condition -- contains facets
25 AND propDef.[Name] = @PropertyName) -- property of proper name
26 SELECT PageId
27 FROM PageRefs
28 WHERE (RowNumber Between @RowStart and @RowEnd) or (@PageSize = 0);
29END
The procedure searches the database and return the results on a chunk specified by @PageNumber, where each chunk is of size specified by @PageSize. If e.g. on the first page you only have a place for first 3 articles you can specify an @Offset for the “Next page »”, otherwise you can set the @offset to 0.
With a little effort you can adapt this stored procedure to do your job or just paged search outside of EPiServer. From the EPiServer point of view however you might find it a caveat, that you need to create your own paged repeater and query the server for the number of pages, which can be done easily with another variant of this stored procedure that asks for count of the same result set and divides it by a number of pages. But then again, if you want it to be done right, it’s usually no substitute to the long way round.
The stored procedure skips a lot of the complexity that it should probably have - meaning it only provides the search in reverse chronological order and disregards whether the page expired as well as any globalization settings, this has been omitted since I did not need it in my project and didn’t really want to add it here not to loose the clarity of the paged searches in Microsoft SQL Server. Just a place to start.
Comments