内部联接是否存在性能问题? [英] Is there any performance issue with Inner Join?

问题描述

当前，我在sp中使用了很多内部联接(大约7)，这对sp性能没有影响.左外连接的性能要好于内连接.

Currently I am using lot of inner join's(around 7) in my sp, does it have any impact on sp performance. does left outer join gives better performance then inner join.

如果我要加入两个具有列id和id1的表a和b，则两个表r都不能为空.我想在这里我可以进行内部连接，因为这些列被索引了.

one more thing if i m joining two tables a and b which has column id and id1, both r not nullable. i suppose here i can go for inner join as these columns r indexed.

推荐答案

外部联接比内部联接更昂贵.我要说的将引起许多争议.如果您正确地调整了数据库，并且没有做任何愚蠢的事情，并且您使用的是专业实力的RDBMS，那么7个内部联接就不成问题.

Outer joins are more expensive than inner joins. What I am about to say is going to be controversial to many. If you tune the database right and if you don't do anything goofy and if you are using a professional strength RDBMS, then 7 inner joins shouldn't be a problem.

数据库调整是什么意思?数据库调优有很多内容，但是最显而易见的检查是确保您始终连接被索引的列.

What do I mean by database tuning? There is a lot to database tuning but the most obvious thing to check is to make sure that you always join over columns that are indexed.

我傻傻的意思是什么?不要在联接条件中使用OR运算符.尝试使连接保持单一比较，例如一个表中的外键等于另一表中的主键.尝试将所有关键字段都键入为整数.

What do I mean by goofy? Don't use the OR operator in your join condition. Try to keep your joins over a single comparison like a foreign key in one table equaling the primary key in the other table. Try to keep all your key fields typed as integers.

如果确实遇到性能问题，那么一定要研究有问题的查询的执行计划.例如，在连接非常大的表时，您可能会遇到问题，因为表太大，以至于索引扫描都太慢.您可能需要反规范化并提供其他过滤以减少扫描时间.不要试图预料到这一点.最好只有在遇到实际性能情况后，才能最好地进行非规范化.

If you do run into performance problems, then be sure to study the execution plan of the offending query. For example, you might run into problems when joining over really large tables, so large that even an index scan is too slow. You may have to denormalize and provide additional filtering to cut down on the scan times. Don't try to anticipate this. Denormalization is best done scarcely and only after you run into real world performance situations.

这篇关于内部联接是否存在性能问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！