SQL - 有条件排除连接的最高效方式? (如果可能的话)
如果我有以下表结构...
Table 1: BlogPost
PostId | Name | Text
Table 2: Tags
TagId | Tag
Table 3: BlogPostTag
PostId | TagId
以及以下存储过程...
CREATE PROCEDURE SearchBlogPosts
@tagstring nvarchar(max),
AS
BEGIN
DECLARE @searchTags TABLE (Tag varchar(50));
IF @tagstring IS NOT NULL AND @tagstring <> ''
BEGIN
INSERT INTO @tags SELECT s AS tag FROM dbo.Split(',',@tagstring);
END
SELECT * FROM BlogPost b
JOIN BlogPostTags bt on bt.PostId = b.PostId
JOIN Tags t on t.TagId = bt.TagId
JOIN @searchTags st ON st.Tag = t.Tag
...
(Other Joins and where clauses may exist below here)
END
...如果 @tagstring 为空或空白,我可以排除标记表上的联接的最“性能”方式是什么?
If I have the following table structure...
Table 1: BlogPost
PostId | Name | Text
Table 2: Tags
TagId | Tag
Table 3: BlogPostTag
PostId | TagId
And the following stored procedure...
CREATE PROCEDURE SearchBlogPosts
@tagstring nvarchar(max),
AS
BEGIN
DECLARE @searchTags TABLE (Tag varchar(50));
IF @tagstring IS NOT NULL AND @tagstring <> ''
BEGIN
INSERT INTO @tags SELECT s AS tag FROM dbo.Split(',',@tagstring);
END
SELECT * FROM BlogPost b
JOIN BlogPostTags bt on bt.PostId = b.PostId
JOIN Tags t on t.TagId = bt.TagId
JOIN @searchTags st ON st.Tag = t.Tag
...
(Other Joins and where clauses may exist below here)
END
...what is the most "performant" manner in which I could exclude the joins on the tag tables if @tagstring is null or blank?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
指定条件连接的唯一方法(也是最好的方法)是使用不同的查询:
SQL 是一种声明性数据访问语言,而不是应用程序命令式处理语言。您声明的任何查询都必须创建一个在所有情况下都有效的访问路径。在查询中使用条件逻辑是最糟糕的事情,它会强制访问计划通常扫描所有可能的数据,因为它们无法在创建计划时确定条件是真还是假。 > 时间。
The only way (and also the best way) to specify conditional joins is to have distinct queries:
SQL is a declarative data access language, not your application imperative processing language. Any query you declare has to create an access path that works in all cases. Having conditional logic in the query is the worst thing you can do, it forces access plans that usually scan all possible data because they cannot determine if conditions are true or false at plan creation time.
我不认为指定左连接会导致性能损失(我假设如果 @tagstring 为空或空白,则 @searchTags 为空)。
或者您不想因为在标签上加入@searchTags而招致性能损失?
在您的示例中并不清楚 @searchtags 中的数据来自何处。
服务器应该足够聪明,不会在存在这种情况的情况下尝试加入。我认为在任何情况下这都不会导致性能显着下降。
如果您出于某种原因希望根本不让列显示在结果中,则具有两个不同查询的 IF...ELSE 块是实现此目的的最简单方法。
I don't think specifying a left join would incur a performance penalty (I'm assuming @searchTags is empty if @tagstring is null or blank).
Or do you not want to incur a performance penalty from joining @searchTags on tags?
It's not really clear in your example where data in @searchtags comes from.
The server should be smart enough to not attempt to join at all with that condition present. I wouldn't think it's something that should cause any significant performance degradation in any case.
If you're looking to not have the columns show up in the result at all for some reason an IF...ELSE block with two different queries is the easiest way to accomplish that.
我认为您可以获得的最佳性能是在 @tagstring 为空或空白时不运行查询。由于所有内部联接和 @searchTags 都没有行,因此您永远不会返回行。
因此,您可以将 SELECT 语句移至 IF 语句的 when true 块中。
I think the best performance you can gain is not to run the query if @tagstring is null or blank. With all inner joins and @searchTags having no rows, you'll never get rows returned.
So, you can move the SELECT statement into the IF statement, when true block.