在t中按父路径筛选包含驱动路径的结果。
在t中按父路径筛选包含驱动路径的结果。
提问于 2014-03-08 09:32:05
我有一个表，其中包含UserIds到磁盘上某些路径(例如\\UNCserver\path或C:\user\has\a\folder)的映射。我控制数据，数据库中没有尾随的\符号。
定期地，我需要选择具有指定路径的父路径的用户in。例如，如果我在\\superserver\cluster\2中有一个事件，我希望获得所有具有以下任一或全部路径的用户ID：
\\superserver\cluster\2
\\superserver\cluster
\\superserver我有一个存储过程就是这样做的，但由于我使用的字符串上的操作效率极低--仅对10000条UserPaths记录，我就可以将CPU加载到50%，只连续调用几百次。
我如何优化这个程序？
CREATE PROCEDURE [dbo].[SelectUserIdsWithPath]
@Path nvarchar(MAX)
AS
BEGIN
SET NOCOUNT ON;
IF (@Path IS NOT NULL)
BEGIN
DECLARE @TempPath NVARCHAR(MAX)
SET @TempPath = SUBSTRING(@Path, 0, LEN(@Path) + 1 - CHARINDEX('\', REVERSE(@Path)))
IF (LEN(@Path) - LEN(REPLACE(@Path, '\', '')) = 1) --we need to process path C:\
BEGIN
SET @TempPath = @TempPath + '\';
END
INSERT INTO Results(UserId)
SELECT DISTINCT UserId FROM UserPaths
WHERE
UserId NOT IN (SELECT UserId FROM Results)
AND (Path = @Path
OR CHARINDEX(Path, @TempPath, 0) <> 0)
END
END更新我现在已经改变了我的应用程序中的逻辑，以便在应用程序中完成父路径的计算，这可能改进了一些东西，但是性能仍然很差。下面是更新的proc清单：
CREATE PROCEDURE [dbo].[SelectUserIdsWithPath]
@Path NVARCHAR(MAX),
@ParentPath NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
IF (@Path IS NOT NULL AND @ParentPath IS NOT NULL)
BEGIN
INSERT INTO Results(UserId)
SELECT DISTINCT UserId FROM UserPaths
WHERE
UserId NOT IN (SELECT UserId FROM Results)
AND (Path = @Path
OR CHARINDEX(Path, @ParentPath, 0) <> 0)
END
END所以罪魁祸首显然是CHARINDEX()呼叫。不幸的是，我仍然在等待基础设施来确认我们是否可以打开全文索引，但是还有其他的选择吗？
回答 2
Stack Overflow用户
发布于 2014-03-09 06:38:09
也许使用cte来提取父文件夹。就像这样：
create procedure SelectUserIdsWithPath
@path varchar(250)
as begin
With c
As
(Select cast(path as varchar(500)) path
from (Select @path path) t
Union all select
Cast(substring(path,0,len(path)-charindex('\',reverse(path),0)+1) as varchar(500))
From c
where charindex('\',reverse(substring(path,0,len(path)-charindex('\',reverse(path),0)+1) ),0)>1)
Select distinct userid from userpaths up where exists(select * from c where c.path=up.path)
endStack Overflow用户
发布于 2014-03-09 06:39:21
我怀疑您是否需要全文搜索；10k行是相当小的数量。这里可能有几件事情对性能有不同程度的影响。
下面的任何示例都是基于原始proc (因为这应该很好)，但是只要将@TempPath更改为@ParentPath，就可以很容易地适应更新的proc。
- 不是性能问题，但Server启动索引是1，而不是0。因此SUBSTRING和CHARINDEX应该使用1而不是0。
- 你为什么要使用NVARCHAR(最大)？如果您知道您的路径中没有超过4000个字符，则最好使用NVARCHAR( 4000 )作为输入参数数据类型以及局部变量数据类型。
- 查林地克斯中的两个字段似乎被转换为签名：
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )。 所以应该是：CHARINDEX(@TempPath, Path)。 - 无论如何，您似乎并不需要CHARINDEX。你应该接受以下几点：
[Path] = @TempPath OR [Path] LIKE @TempPath + N'\%'请注意，@TempPath现在在这两种情况下都使用。 如果使用原始proc，那么一定要删除IF (LEN(@Path)...BEGIN...END，否则，不要在应用程序代码中(对于C:\ case)添加尾部\ @ParentPath。在这两种情况下，LIKE可能比CHARINDEX好，因为尾随'%‘，而没有前导'%’基本上是一个String.StartsWith，而CHARINDEX是一个String.Contains。 - 插入的选择可能会被改进，方法是将DISTINCT和NOT分离成第二个查询，使用temp表来保存第一个查询的结果：
INSERT INTO #TempResults(UserId) SELECT UserId FROM UserPaths WHERE [Path] = @TempPath OR [Path] LIKE @TempPath + N'\%'INSERT INTO Results(UserId) SELECT DISTINCT UserId FROM #TempResults WHERE UserId NOT IN (SELECT UserId FROM Results)您应该在这两个查询中测试NOT IN条件，以查看它在哪里工作得更好。 - 鉴于此proc被称为“每分钟数百次”(通过Service )，并且结果表“每分钟都会被清除”：如果有可能，将昂贵的操作(即通过DISTINCT而不是uniqueness保证UserId的唯一性)从每分钟运行数百次的进程移到每分钟运行一次的操作。因此，a)删除结果表上的唯一约束，b)更新使用结果表的过程以包含不同的，c)使用以下简化INSERT...SELECT：
INSERT INTO Results(UserId) SELECT UserId FROM UserPaths WHERE [Path] = @TempPath OR [Path] LIKE @TempPath + N'\%'如果Service配置为通过多个线程运行此proc，那么您还会遇到INSERT操作与NOT子查询的SELECT之间的争用。删除NOT IN子查询将避免此争用。 在得到关于如何确定有效匹配的澄清之后，我将更新WHERE条件。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22267574
复制相关文章
相似问题
腾讯云开发者
