SQL 是否有连接的替代方法来提高性能？

Is there an alternative to joins to increase performance?

提问by hrishi

Is there an alternative to joins to increase performance?

是否有连接的替代方法来提高性能？

Edit (gbn): related to join-or-correlated-subquery-with-exists-clause-which-one-is-better

编辑 (gbn)：与join-or-correlated-subquery-with-exists-clause-which-one-is-better 相关

---

---

Why didn't anyone mention about nested loop joins?

为什么没有人提到嵌套循环连接？

回答by marc_s

Not an "alternate" way to JOINs, but a tip to increase JOIN performance: in SQL Server that a lot of folks don't know is that you should always put a non-clustered index on a foreign key column. Several folks believe SQL Server does this automatically - it does not.

不是 JOIN 的“替代”方式，而是提高 JOIN 性能的一个技巧：在 SQL Server 中，很多人不知道的是，您应该始终将非聚集索引放在外键列上。一些人认为 SQL Server 会自动执行此操作 - 事实并非如此。

So if you have a table Customer, it probably has a primary key something like CustomerID. SQL Server will put an index on that automatically.

因此，如果您有一张表Customer，它可能有一个类似于CustomerID. SQL Server 将自动在其上放置索引。

However, if you have a table Orderthat has a foreign key relationship with Customer, there is by default no index on the column Order.CustomerID. But such an index is very useful and helpful for joins and lookups, so that's a best practice I always recommend: put an index on all your foreign key columns in a table.

但是，如果您有一个Order与 具有外键关系的表，则Customer默认情况下该列上没有索引Order.CustomerID。但是这样的索引对于连接和查找非常有用并且很有帮助，因此这是我始终建议的最佳实践：在表中的所有外键列上放置索引。

回答by Bill Karwin

Strategies for mitigating performance of joins:

降低连接性能的策略：

• Indexing
• Denormalization
• Caching results
• Using a NoSQL database (no SQL = no joins, q.e.d.)

• 索引
• 非规范化
• 缓存结果
• 使用 NoSQL 数据库（无 SQL = 无连接，qed）

All of these strategies optimize for specificqueries. You can't make a general-purpose solution that can improve allqueries.

所有这些策略都针对特定查询进行了优化。您无法制定可以改进所有查询的通用解决方案。

回答by Gus Cavalcanti

Using (NOLOCK) on joins may increase performance, if you want/can read uncommitted records. When should you use "with (nolock)"

如果您想要/可以读取未提交的记录，则在连接上使用 (NOLOCK) 可能会提高性能。 什么时候应该使用“with (nolock)”

回答by gbn

From your other question

从你的另一个问题

select * 
from ContactInformation c 
where exists (select * from Department d where d.Id = c.DepartmentId )

select * 
from ContactInformation c 
inner join Department d on c.DepartmentId = d.Id  

If you want output from both tables, then you have option other then JOIN. The 2nd query here.

如果您想要两个表的输出，那么您可以选择其他选项而不是 JOIN。这里的第二个查询。

If it's slow, then generally:

如果它很慢，那么通常：

• you have primary key/indexes?
• consistent datatypes (the DepartmentId/id columns)
• don't use SELECT *

• 你有主键/索引吗？
• 一致的数据类型（DepartmentId/id 列）
• 不要使用 SELECT *

回答by Mostafa Elmoghazi

Syntactically, there is no alternative way but just a few techniques that might help you regarding query performance with very large volumes of data:

从语法上讲，没有其他替代方法，只有一些技术可以帮助您处理大量数据的查询性能：

• If applicable and the number of columns returned by the query are not many you can use INTERSECT, EXCEPT OR UNION
• If the query is very complex and is of many steps on very large volumes of data, divide and conquer with temp tables.
• If the query is back to a report presenting some information that could be of yesterday's image of the data you can use sql server agent jobs to calculate and save the result in a table to be used as a back for the report instead of the query or as an alternative use indexed views to get the result.
• If some information like count of rows in a table takes too long to get you can use the metadata tables of the table to get such piece of information.This is not only for the count of rows in a table.You can get a lot of information from the metadata with no need to calculate it.(Keep in touch with this site)

• 如果适用并且查询返回的列数不多，您可以使用 INTERSECT, EXCEPT OR UNION
• 如果查询非常复杂并且涉及大量数据的许多步骤，请使用临时表进行分而治之。
• 如果查询返回到一个报告，提供一些可能是昨天数据图像的信息，您可以使用 sql server 代理作业来计算结果并将结果保存在一个表中，以用作报告而不是查询或作为替代使用索引视图来获得结果。
• 如果某些信息如表中的行数获取时间太长，您可以使用该表的元数据表来获取此类信息。这不仅适用于表中的行数。您可以获得很多元数据中的信息，无需计算。（与本站保持联系）

回答by Oded

Relational databases are optimized to use Joins, so in most cases using the is the most preformant thing you can do.

关系数据库经过优化以使用联接，因此在大多数情况下，使用联接是您可以做的最重要的事情。

If your queries are slow, you need to optimize them - perhaps you are missing an index or two, perhaps you can rewrite the where clauses to reduce the number of returned rows.

如果您的查询很慢，您需要优化它们——也许您缺少一两个索引，也许您可​​以重写 where 子句以减少返回的行数。

You can use sub-queriesand temp tables, but chances are that a join would still be fastest. You will have to test in your own environment to see.

您可以使用子查询和临时表，但有可能连接仍然是最快的。您必须在自己的环境中进行测试才能看到。

回答by Shankar R10N

In any non-trivial DB driven application there is no way ... for you to avoid joins.

在任何非平凡的数据库驱动应用程序中，您都无法避免连接。

Joins...themselves are not the root cause of the problem but bad performance could be the result of anything from poorly written queries to poorly designed database.

联接...本身不是问题的根本原因，但性能不佳可能是由于查询编写不当到数据库设计不当等任何原因造成的。

Yes...in some cases joins encapsulated in stored functionscan be avoided by using prepared fields. That is, if you are sure you will be needing a resultant value from a certain join for repeated use..you might as well calculate it once and store it for repeated use.

是的...在某些情况下，可以通过使用准备好的字段来避免封装在存储函数中的连接。也就是说，如果您确定您将需要某个连接的结果值以供重复使用..您不妨计算一次并存储它以供重复使用。

Correlated Sub-queries are another alternative.

相关子查询是另一种选择。

In general if you are looking to sharpen your skill the ...question you should be asking is: How to write efficient queries ?

一般来说，如果你想提高你的技能，你应该问的问题是：如何编写高效的查询？