性能:子查询或联接 [英] Performance: Subquery or Joining

问题描述

我对子查询的性能/连接另一个表有一个疑问

I got a little question about performance of a subquery / joining another table

INSERT
INTO Original.Person
  (
    PID, Name, Surname, SID
  )
  (
    SELECT ma.PID_new , TBL.Name , ma.Surname, TBL.SID 
    FROM Copy.Person TBL , original.MATabelle MA
    WHERE TBL.PID         = p_PID_old
      AND TBL.PID         = MA.PID_old
  );

这是我的SQL，现在这件事运行了大约一百万次或更多. 我的问题是什么会更快?

This is my SQL, now this thing runs around 1 million times or more. My question is what would be faster?

• 如果我将TBL.SID更改为(Select new from helptable where old = tbl.sid)

• If I change TBL.SID to (Select new from helptable where old = tbl.sid)

或

• 如果我将'HelpTable'添加到from并在where中进行联接?

• If I add the 'HelpTable' to the from and do the joining in the where?

编辑1
好吧，此脚本的运行量与r人的数量一样.

edit1
Well, this script runs only as much as there r persons.

我的程序有2个模块，一个模块填充MaTabelle，另一个模块传输数据.该程序确实将2个数据库合并在一起，因此有时使用相同的Key.
现在，我正在研究一种不存在重复密钥的解决方案.

My program has 2 modules one that populates MaTabelle and one that transfers data. This program does merge 2 databases together and coz of this, sometimes the same Key is used.
Now I'm working on a solution that no duplicate Keys exists.

我的解决方案是制作一个帮助表".密钥(SID)的所有者生成一个新密钥，并将其写入"HelpTable".使用此键的所有其他表都可以从帮助表"中读取它.

My solution is to make a 'HelpTable'. The owner of the key(SID) generates a new key and writes it into a 'HelpTable'. All other tables that use this key can read it from the 'HelpTable'.

edit2
我刚刚想到了一些事情:
如果作为键的表可以为null(未链接的外键) 那么这将不适用于from或?

edit2
Just got something in my mind:
if a table as a Key that can be null(foreignkey that is not linked) then this won't work with the from or?

推荐答案

包括Oracle在内的现代RDBM可以优化大多数联接和子查询，直至达到相同的执行计划.

Modern RDBMs, including Oracle, optimize most joins and sub queries down to the same execution plan.

因此，我将以对您最简单的方式来编写您的查询，并着重确保您已完全优化索引.

Therefore, I would go ahead and write your query in the way that is simplest for you and focus on ensuring that you've fully optimized your indexes.

如果您提供最终查询和数据库架构，我们也许能够提供详细的建议，包括有关潜在锁定问题的信息.

If you provide your final query and your database schema, we might be able to offer detailed suggestions, including information regarding potential locking issues.

修改

以下是一些适用于您的查询的常规提示:

Here are some general tips that apply to your query:

• 对于联接，请确保要联接的列上有索引.确保将索引应用于两个表中的联接列.您可能会认为您只需要一个方向的索引，但是您应该同时索引两个索引，因为有时数据库确定最好以相反的方向进行连接.
• 对于WHERE子句，请确保在WHERE中提到的列上具有索引.
• 要插入许多行，最好将它们全部插入一个查询中.
• 要在具有聚簇索引的表上进行插入，最好为聚簇索引插入增量值，以便将新行附加到数据的末尾.这避免了重建索引，并且通常避免了对现有记录的锁定，这将减慢对现有行的SELECT查询.基本上，插入对系统的其他用户而言不再那么痛苦.

这篇关于性能:子查询或联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！