sql - 在 SQL Server 2008中,改进和加速搜索结果

  显示原文与译文双语对照的内容
0 0

我的Having 问题从我的复杂搜索算法获得良好的搜索结果。 可以理解,我应该使用 on,但是我对 SQL Server 很新,并且我不知道如何创建Full-Text-Search索引。 所以,我必须用我现在拥有的。 数据库成员 table 有超过一百万条记录,因此检索速度慢。

示例输入


@CriteriaStr = 'Attorney New York'
@PageSize = 10
@PG = 1

请允许我向你展示代码:

主要搜索SQL脚本


IF @MODE = 'MEMBER-MAIN-SEARCH'
 BEGIN
 SELECT @TotalPages = CEILING(COUNT(*)/@PageSize)
 FROM Member
 WHERE MemberID IN (SELECT MemberID FROM sbuser.tf_MemberSearchCriteria(REPLACE(@CriteriaStr,' ',',')))
 AND Claimed = 'Y'
 AND Viewable = 'Y';

 WITH MemberSearchResults AS
 (
 SELECT ROW_NUMBER() OVER(ORDER BY a.Claimed DESC) AS RowNum,
 a.MemberID, -- 1 
 a.FirstName, -- 2
 a.LastName, -- 3
 a.MemberDisplayName AS DisplayName, -- 4
 a.UserName, -- 5
 a.LastLogin, -- 6
 a.PrCity, -- 7
 a.BusinessName, -- 8
 a.ShortDesc, -- 9
 a.PrAddr1, -- 10
 a.PrAddr2, -- 11
 a.PrZip, -- 12
 b.Abbr, -- 13
 c.Country, -- 14
 a.AvatarMed, -- 15
 a.Gender, -- 16
 d.Domain, -- 17
 a.Claimed, -- 18
 a.PrPhone, -- 19
 a.PrShowAddress, -- 20
 a.ProfileTypeID, -- 21
 @TotalPages AS TotalPages -- 22
 FROM Member a
 RIGHT JOIN State b ON b.StateID = a.PrStateID
 INNER JOIN Country c ON c.CountryID = a.PrCountryID
 INNER JOIN Region d ON d.RegionID = a.MemberRegionID
 WHERE a.MemberID IN (SELECT MemberID FROM sbuser.tf_MemberSearchCriteria(REPLACE(@CriteriaStr,' ',',')))
 AND Claimed = 'Y'
 AND Viewable = 'Y'
 )
 SELECT * 
 FROM MemberSearchResults
 WHERE RowNum BETWEEN (@PG - 1) * @PageSize + 1 AND @PG * @PageSize
 ORDER BY Claimed DESC
 END

TF_MEMBERSEARCHCRITERIA


USE [storeboard]
GO
/****** Object: UserDefinedFunction [sbuser].[tf_MemberSearchCriteria] Script Date: 11/30/2012 08:22:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [sbuser].[tf_MemberSearchCriteria](@CriteriaStr varchar(255))
RETURNS @OUT TABLE (MemberID bigint)

AS

BEGIN
 DECLARE @Keyword varchar(150)


 DECLARE CUR CURSOR FOR SELECT Keyword from sbuser.tf_Keywords(@CriteriaStr)
 OPEN CUR
 FETCH NEXT FROM CUR INTO @Keyword
 WHILE @@FETCH_STATUS = 0
 BEGIN
 --INSERT INTO @OUT
 --SELECT MemberID FROM Member WHERE ShortDesc LIKE '%' + @Keyword + '%' AND Claimed = 'Y' AND MemberID NOT IN (Select MemberID FROM @OUT)

 --INSERT INTO @OUT
 --SELECT MemberID FROM Member WHERE UserName LIKE '%' + @Keyword + '%' AND Claimed = 'Y' AND memberID NOT IN (Select MemberID FROM @OUT)

 INSERT INTO @OUT
 SELECT MemberID FROM Member WHERE BusinessName LIKE '%' + @Keyword + '%' AND Claimed = 'Y' AND memberID NOT IN (SELECT MemberID FROM @OUT)

 INSERT INTO @OUT
 SELECT MemberID FROM Member WHERE FirstName + LastName LIKE '%' + REPLACE(@Keyword,' ','') + '%' AND Claimed = 'Y' AND memberID NOT IN (SELECT MemberID FROM @OUT)

 INSERT INTO @OUT
 SELECT a.MemberID
 FROM MemberBusinessCat a
 LEFT JOIN BusinessCat b ON b.BusinessCatID = a.BusinessCatID
 LEFT JOIN BusinessCat c ON c.BusinessCatID = b.ParentID
 WHERE b.CategoryName LIKE '%' + @Keyword + '%' OR c.CategoryName LIKE '%' + @Keyword + '%'
 AND a.MemberID NOT IN (SELECT MemberID FROM @OUT)


 FETCH NEXT FROM CUR INTO @Keyword

 END
 CLOSE CUR
 DEALLOCATE CUR


 DELETE FROM @OUT WHERE MemberID IS NULL

 RETURN
END

TF_KEYWORDS


USE [storeboard]
GO
/****** Object: UserDefinedFunction [sbuser].[tf_Keywords] Script Date: 11/30/2012 08:50:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [sbuser].[tf_Keywords] 
 (@KeywordList varchar(4000)
)
RETURNS @OUT TABLE (
 Keyword varchar(50))

AS
 BEGIN
 DECLARE @NEXTPOS bigint
 DECLARE @NEXTWORD varchar(50)
 DECLARE @LISTLEN int
 DECLARE @FAILSAFE int

 SET @FAILSAFE = 0

 SET @KeywordList = @KeywordList + ','
 WHILE @KeywordList <> '' AND @FAILSAFE <50
 BEGIN
 SET @NEXTPOS = CHARINDEX(',',@KeywordList)
 SET @LISTLEN = LEN(@KEYWORDLIST)
 SET @NEXTWORD = LTRIM(RTRIM(REPLACE(LEFT(@KeywordList,@NEXTPOS),',','')))
 SET @KEYWORDLIST = RIGHT(@KEYWORDLIST,@LISTLEN-@NEXTPOS)


 INSERT INTO @OUT
 SELECT @NEXTWORD

 SET @FAILSAFE = @FAILSAFE + 1

 END

 RETURN
 END

我想知道,是否可以按照任何一个成员的结果数来订购 TF_MEMBERSEARCHCRITERIA 。 一个会员可以在纽约出现,这将使他们在结果 3次显示。 脚本删除重复,但是可以有一种计算 3结果的方法,然后按说明顺序排序。

我仍然是相对新的,所以我仍然没有完全理解SQL的复杂部分,所以请轻松。

感谢你的帮助,neojakey

时间: 原作者:

0 0

我给出了一个很大的答案,如果任何错误让我知道我注释了改进区域

Advice1: -
1 ) 使用索引视图并将可以搜索列添加到 1 coumn像必须将列memberID作为主键和 searchtext nvarchar(MAX) --添加所有搜索coumns添加所有搜索
--名+ BusinessName 2 ) 通过它创建全文索引,并使用分部词搜索或者排序结果 freetextable 。
3 ) 如果占用磁盘空间小,则简单且简单

Advice2: - 1 ) 仅对最后一个 10行使用所有联接。 INTHEMO_Authentication你的代码


 ; WITH 
 MemberSearchResults AS
 (
 SELECT ROW_NUMBER() OVER(ORDER BY a.Claimed DESC) AS RowNum,
 a.MemberID, ---- bring the primary and the columns need to orderby only dont join with unnessary tables inthis step 
 a.Claimed

 FROM Member a
 --RIGHT JOIN State b ON b.StateID = a.PrStateID
 --INNER JOIN Country c ON c.CountryID = a.PrCountryID
 --INNER JOIN Region d ON d.RegionID = a.MemberRegionID
 WHERE a.MemberID IN (SELECT MemberID FROM sbuser.tf_MemberSearchCriteria(REPLACE(@CriteriaStr,' ',',')) group by MemberID)
 ---- in that function dont use like --NOT IN (SELECT MemberID FROM @OUT) its a small improvement u can do here 
 AND Claimed = 'Y' ---- ur checking this condition on function tooo then no need here i think 
 AND Viewable = 'Y' ---- ur checking this condition on function tooo then no need here i think 
 )
 SELECT --a.MemberID, -- 1 
 --a.FirstName, -- 2
 --a.LastName, -- 3
 --a.MemberDisplayName AS DisplayName, -- 4
 --a.UserName, -- 5
 --a.LastLogin, -- 6
 --a.PrCity, -- 7
 --a.BusinessName, -- 8
 --a.ShortDesc, -- 9
 --a.PrAddr1, -- 10
 --a.PrAddr2, -- 11
 --a.PrZip, -- 12
 --b.Abbr, -- 13
 --c.Country, -- 14
 --a.AvatarMed, -- 15
 --a.Gender, -- 16
 --d.Domain, -- 17
 --a.Claimed, -- 18
 --a.PrPhone, -- 19
 --a.PrShowAddress, -- 20
 --a.ProfileTypeID, -- 21
 --@TotalPages AS TotalPages -- 22
 FROM MemberSearchResults cross join (SELECT COUNT(*) FROM MemberSearchResults) AS @TotalPages -- u have to do total page count like this 
 --RIGHT JOIN State b ON b.StateID = a.PrStateID
 --INNER JOIN Country c ON c.CountryID = a.PrCountryID
 --INNER JOIN Region d ON d.RegionID = a.MemberRegionID
 WHERE RowNum BETWEEN (@PG - 1) * @PageSize + 1 AND @PG * @PageSize 
 ORDER BY Claimed DESC

最后一件事,如果你可以使用dity读取,你可以使用它。

...