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.

CREATE Procedure PagedSearch
    @Condition varchar(1024),
    @PropertyName varchar(1024),
    @PropertyType varchar(1024),
    @PageSize int,
    @PageNumber int,
    @Offset int
AS
BEGIN
    DECLARE @RowStart int
    DECLARE @RowEnd int
 
    SET @RowStart = @PageSize * @PageNumber + @Offset;
    SET @RowEnd = @RowStart + @PageSize + @Offset;
 
    WITH PageRefs AS
        (SELECT page.pkID as PageId,
            ROW_NUMBER() OVER (ORDER BY page.StartPublish DESC) as RowNumber
            FROM tblPage page, tblProperty propValue, tblPageDefinition propDef
            WHERE page.pkID = propValue.fkPageID
                AND propValue.fkPageDefinitionID = propDef.pkID
                AND propDef.fkPageDefinitionTypeID = @propertyType -- is proper type
                AND propValue.Searchable -- the property is searchable
                AND propValue.String like @Condition -- contains facets
                AND propDef.[Name] = @PropertyName) -- property of proper name
    SELECT PageId
        FROM PageRefs
        WHERE (RowNumber Between @RowStart and @RowEnd) or (@PageSize = 0);
END

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.

(No Ratings Yet)

Loading...

This entry (Permalink) was posted on Monday, May 21st, 2007 at 5:04 pm and is filed under EPiServer, Microsoft SqlServer, Web applications. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response , or trackback from your own site.