AspNetCommerce中的一个带分页和排序的搜索功能的存储过程
刚翻了翻<<>SQL SERVER 性能调校一书>,原来LIKE N'%' + @SearchKey + N'%'这样并不符合书中所说的SARGs原则,并无法
利用有序的数据结构搭配二分查找法来快速寻数据,如果写成LIKE @SearchKey + N'%'就符合了,但是如何能做到
LIKE N'%' + @SearchKey + N'%'的功能,但又实现符合SARGs原则?
ALTER PROCEDURE dbo.vw_Commerce_ProductBrowse_PagingQuickSearch
(
@SearchKey nvarchar(200),--要搜索的关键字
@SortType int,--排序类型,根据排序类型的整型序号来决定使用哪种排序方式,<DEFAULT>值为0
@StartRow int,--表示从第几行开始,与PageIndex是不相同的!
@MaxRows int --表示列出多少行
)
AS
/* SET NOCOUNT ON */
--1.设置页范围
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
DECLARE @TotalRecords INT
SET @PageLowerBound = @StartRow
SET @PageUpperBound = @MaxRows -1 + @PageLowerBound
--2.创建一个临时表以保存搜索结果
CREATE TABLE #PageIndexForProductBrowse
(
IndexId int IDENTITY (0, 1) NOT NULL,
SKU int
)
--3.把数据添加进临时表
INSERT INTO #PageIndexForProductBrowse (SKU)
SELECT b.SKU
FROM dbo.vw_Commerce_ProductBrowse b
WHERE (b.Name LIKE N'%' + @SearchKey + N'%') OR
(b.ShortDescription LIKE N'%' + @SearchKey + N'%') OR
(b.LongDescription LIKE N'%' + @SearchKey + N'%') OR
(b.SkuDesc LIKE N'%' + @SearchKey + N'%')
--4.检索出受影响的行数(总共的记录数)
SELECT @TotalRecords = @@ROWCOUNT
--5.联合临时表和vw_Commerce_ProductBrowse检索出合符条件的数据
--*创建要动态运行的SQL字符串*
DECLARE @SQLString nvarchar(500)--动态SQL字符串命令
DECLARE @Parm nvarchar(500)
SET @SQLString =
N'SELECT b.ProductId, b.Name, b.ShortDescription, b.LongDescription, b.CategoryId, b.ParentCategoryId, b.SKU, '+
N' b.SkuDesc, b.RetailPrice, b.ActualPrice, b.VIPPrice, b.DiamondVIPPrive, b.QtyOnHand, b.Unit '+
N'FROM dbo.vw_Commerce_ProductBrowse b,#PageIndexForProductBrowse p '+
N'WHERE b.SKU = p.SKU AND p.IndexId >= @MyPageLowerBound AND p.IndexId <= @MyPageUpperBound '
SELECT @SQLString = @SQLString + CASE @SortType
WHEN 0 THEN (N'ORDER BY b.ActualPrice')
WHEN 1 THEN (N'ORDER BY b.ActualPrice DESC')
WHEN 2 THEN (N'ORDER BY b.ActualPrice DESC')
ELSE NULL
END
--*定义各个参数的数据类型*
SET @Parm = N'@MyPageLowerBound INT,@MyPageUpperBound INT'
--*设置参数值*
--*正式执行*
EXECUTE sp_executesql @SQLString,
@Parm,
@MyPageLowerBound = @PageLowerBound,
@MyPageUpperBound = @PageUpperBound
--返回合符条件的总记录数
RETURN @TotalRecords
利用有序的数据结构搭配二分查找法来快速寻数据,如果写成LIKE @SearchKey + N'%'就符合了,但是如何能做到
LIKE N'%' + @SearchKey + N'%'的功能,但又实现符合SARGs原则?
ALTER PROCEDURE dbo.vw_Commerce_ProductBrowse_PagingQuickSearch
(
@SearchKey nvarchar(200),--要搜索的关键字
@SortType int,--排序类型,根据排序类型的整型序号来决定使用哪种排序方式,<DEFAULT>值为0
@StartRow int,--表示从第几行开始,与PageIndex是不相同的!
@MaxRows int --表示列出多少行
)
AS
/* SET NOCOUNT ON */
--1.设置页范围
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
DECLARE @TotalRecords INT
SET @PageLowerBound = @StartRow
SET @PageUpperBound = @MaxRows -1 + @PageLowerBound
--2.创建一个临时表以保存搜索结果
CREATE TABLE #PageIndexForProductBrowse
(
IndexId int IDENTITY (0, 1) NOT NULL,
SKU int
)
--3.把数据添加进临时表
INSERT INTO #PageIndexForProductBrowse (SKU)
SELECT b.SKU
FROM dbo.vw_Commerce_ProductBrowse b
WHERE (b.Name LIKE N'%' + @SearchKey + N'%') OR
(b.ShortDescription LIKE N'%' + @SearchKey + N'%') OR
(b.LongDescription LIKE N'%' + @SearchKey + N'%') OR
(b.SkuDesc LIKE N'%' + @SearchKey + N'%')
--4.检索出受影响的行数(总共的记录数)
SELECT @TotalRecords = @@ROWCOUNT
--5.联合临时表和vw_Commerce_ProductBrowse检索出合符条件的数据
--*创建要动态运行的SQL字符串*
DECLARE @SQLString nvarchar(500)--动态SQL字符串命令
DECLARE @Parm nvarchar(500)
SET @SQLString =
N'SELECT b.ProductId, b.Name, b.ShortDescription, b.LongDescription, b.CategoryId, b.ParentCategoryId, b.SKU, '+
N' b.SkuDesc, b.RetailPrice, b.ActualPrice, b.VIPPrice, b.DiamondVIPPrive, b.QtyOnHand, b.Unit '+
N'FROM dbo.vw_Commerce_ProductBrowse b,#PageIndexForProductBrowse p '+
N'WHERE b.SKU = p.SKU AND p.IndexId >= @MyPageLowerBound AND p.IndexId <= @MyPageUpperBound '
SELECT @SQLString = @SQLString + CASE @SortType
WHEN 0 THEN (N'ORDER BY b.ActualPrice')
WHEN 1 THEN (N'ORDER BY b.ActualPrice DESC')
WHEN 2 THEN (N'ORDER BY b.ActualPrice DESC')
ELSE NULL
END
--*定义各个参数的数据类型*
SET @Parm = N'@MyPageLowerBound INT,@MyPageUpperBound INT'
--*设置参数值*
--*正式执行*
EXECUTE sp_executesql @SQLString,
@Parm,
@MyPageLowerBound = @PageLowerBound,
@MyPageUpperBound = @PageUpperBound
--返回合符条件的总记录数
RETURN @TotalRecords
