ALTER PROCEDURE [dbo].[pBuildReportSensitiveKeywordsMatch]
AS
BEGIN
SET NOCOUNT ON; DELETE
FROM ReportSensitiveKeywordsMatch -- 查找所有在线Listing DECLARE @keywordsType int, @keywords nvarchar(250) DECLARE cursor_keywords CURSOR FOR
SELECT [Type], Keywords
FROM SensitiveKeywords OPEN cursor_keywords
FETCH NEXT FROM cursor_keywords INTO @keywordsType, @keywords
WHILE @@FETCH_STATUS = 0
BEGIN INSERT INTO ReportSensitiveKeywordsMatch
(
PMID, PMName,
ProductID, PropertyID,
ItemID,
eBayID, eBayName,
[Site], Location,
SKU, Title,
KeywordsType, Keywords,
CreateDate
)
SELECT
u.UserID, u.UserName,
cl.ProductID, cl.PropertyID,
l.ItemID,
e.eBayID, e.eBayName,
l.[Site], l.Location,
l.SKU, l.Title,
@keywordsType, @keywords,
GETDATE()
FROM OpenData.dbo.Listing as l
INNER JOIN OpenData.dbo.eBay as e ON l.eBayID = e.eBayID
INNER JOIN ProductCustomerLabel as cl ON cl.CustomerLabel = l.SKU
INNER JOIN Product as p ON cl.ProductID = p.ProductID
INNER JOIN Users as u ON u.UserID = p.ProductManagerID
WHERE e.CompanyID = 6 -- 内部
AND -- 关键字匹配
(
l.Title LIKE @keywords+' %'
OR l.Title LIKE '% '+@keywords
OR l.Title LIKE '% '+@keywords+' %' OR l.Title LIKE '% '+@keywords+',%'
OR l.Title LIKE '% '+@keywords+'-%'
OR l.Title LIKE '% '+@keywords+'+%'
OR l.Title LIKE '% '+@keywords+'/%'
OR l.Title LIKE '% '+@keywords+'_%' OR l.Title LIKE '%,'+@keywords+' %'
OR l.Title LIKE '%-'+@keywords+' %'
OR l.Title LIKE '%+'+@keywords+' %'
OR l.Title LIKE '%/'+@keywords+' %'
OR l.Title LIKE '%_'+@keywords+' %' OR l.Title LIKE @keywords+',%'
OR l.Title LIKE '%,'+@keywords
OR l.Title LIKE '%,'+@keywords+',%' OR l.Title LIKE @keywords+'-%'
OR l.Title LIKE '%-'+@keywords
OR l.Title LIKE '%-'+@keywords+'-%' OR l.Title LIKE @keywords+'+%'
OR l.Title LIKE '%+'+@keywords
OR l.Title LIKE '%+'+@keywords+'+%' OR l.Title LIKE @keywords+'/%'
OR l.Title LIKE '%/'+@keywords
OR l.Title LIKE '%/'+@keywords+'/%' OR l.Title LIKE @keywords+'_%'
OR l.Title LIKE '%_'+@keywords
OR l.Title LIKE '%_'+@keywords+'_%'
)
AND EXISTS -- 站点对应语言
(
SELECT TOP 1 NULL
FROM fun_LanguageToSite(cl.[Language]) as l2s
WHERE l2s.[Site] = l.[Site]
)
AND NOT EXISTS -- 忽略表
(
SELECT TOP 1 NULL
FROM SensitiveKeywordsOverPass as op
WHERE op.ProductID = cl.ProductID
AND ISNULL(op.PropertyID,0) = ISNULL(cl.PropertyID,0)
AND op.Keywords = @keywords
) FETCH NEXT FROM cursor_keywords INTO @keywordsType, @keywords
END
CLOSE cursor_keywords
DEALLOCATE cursor_keywords
END
SensitiveKeywords表的keywords字段不断的去模糊匹配l.Title字段,但是用到了游标和like关键字,效率非常低,请问有没有其他的优化技巧。现在想用windows服务实现,但是不知道如何拆解这个存储过程。请各位看看。小弟感激不尽!!!
AS
BEGIN
SET NOCOUNT ON; DELETE
FROM ReportSensitiveKeywordsMatch -- 查找所有在线Listing DECLARE @keywordsType int, @keywords nvarchar(250) DECLARE cursor_keywords CURSOR FOR
SELECT [Type], Keywords
FROM SensitiveKeywords OPEN cursor_keywords
FETCH NEXT FROM cursor_keywords INTO @keywordsType, @keywords
WHILE @@FETCH_STATUS = 0
BEGIN INSERT INTO ReportSensitiveKeywordsMatch
(
PMID, PMName,
ProductID, PropertyID,
ItemID,
eBayID, eBayName,
[Site], Location,
SKU, Title,
KeywordsType, Keywords,
CreateDate
)
SELECT
u.UserID, u.UserName,
cl.ProductID, cl.PropertyID,
l.ItemID,
e.eBayID, e.eBayName,
l.[Site], l.Location,
l.SKU, l.Title,
@keywordsType, @keywords,
GETDATE()
FROM OpenData.dbo.Listing as l
INNER JOIN OpenData.dbo.eBay as e ON l.eBayID = e.eBayID
INNER JOIN ProductCustomerLabel as cl ON cl.CustomerLabel = l.SKU
INNER JOIN Product as p ON cl.ProductID = p.ProductID
INNER JOIN Users as u ON u.UserID = p.ProductManagerID
WHERE e.CompanyID = 6 -- 内部
AND -- 关键字匹配
(
l.Title LIKE @keywords+' %'
OR l.Title LIKE '% '+@keywords
OR l.Title LIKE '% '+@keywords+' %' OR l.Title LIKE '% '+@keywords+',%'
OR l.Title LIKE '% '+@keywords+'-%'
OR l.Title LIKE '% '+@keywords+'+%'
OR l.Title LIKE '% '+@keywords+'/%'
OR l.Title LIKE '% '+@keywords+'_%' OR l.Title LIKE '%,'+@keywords+' %'
OR l.Title LIKE '%-'+@keywords+' %'
OR l.Title LIKE '%+'+@keywords+' %'
OR l.Title LIKE '%/'+@keywords+' %'
OR l.Title LIKE '%_'+@keywords+' %' OR l.Title LIKE @keywords+',%'
OR l.Title LIKE '%,'+@keywords
OR l.Title LIKE '%,'+@keywords+',%' OR l.Title LIKE @keywords+'-%'
OR l.Title LIKE '%-'+@keywords
OR l.Title LIKE '%-'+@keywords+'-%' OR l.Title LIKE @keywords+'+%'
OR l.Title LIKE '%+'+@keywords
OR l.Title LIKE '%+'+@keywords+'+%' OR l.Title LIKE @keywords+'/%'
OR l.Title LIKE '%/'+@keywords
OR l.Title LIKE '%/'+@keywords+'/%' OR l.Title LIKE @keywords+'_%'
OR l.Title LIKE '%_'+@keywords
OR l.Title LIKE '%_'+@keywords+'_%'
)
AND EXISTS -- 站点对应语言
(
SELECT TOP 1 NULL
FROM fun_LanguageToSite(cl.[Language]) as l2s
WHERE l2s.[Site] = l.[Site]
)
AND NOT EXISTS -- 忽略表
(
SELECT TOP 1 NULL
FROM SensitiveKeywordsOverPass as op
WHERE op.ProductID = cl.ProductID
AND ISNULL(op.PropertyID,0) = ISNULL(cl.PropertyID,0)
AND op.Keywords = @keywords
) FETCH NEXT FROM cursor_keywords INTO @keywordsType, @keywords
END
CLOSE cursor_keywords
DEALLOCATE cursor_keywords
END
SensitiveKeywords表的keywords字段不断的去模糊匹配l.Title字段,但是用到了游标和like关键字,效率非常低,请问有没有其他的优化技巧。现在想用windows服务实现,但是不知道如何拆解这个存储过程。请各位看看。小弟感激不尽!!!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货