我的用例可以合并到单个查询中，而不会影响性能？ [英] Can my use case be merged into a single query without affecting performance?

问题描述

我主要着眼于提高查询的性能，以及是否能够在单个查询中解决我的一个用例，如下所述：

有两个表：

 表1：EMPLOYEE ，email1，email2，column5，column6）
表2：EMAILLIST（电子邮件）
  

要求是，我想从表EMPLOYEE获取/获取所有记录，条件是email1或email2在EMAILLIST表中没有匹配的条目。简单来说，如果email1或email2在EMAILLIST表中匹配，那么这些记录应该被忽略。

在这种情况下，EMPLOYEE.EMAIL1，EMPLOYEE.EMAIL2和EMAILLIST

我们正在使用PostgreSQL v8.2.3，如果有问题。

$

更新：目前，我们已经实现了这样：从EMPLOYEE表中获取所有记录并存储在Java对象中，并且对于每个条目（for循环），这反过来检查EMAILLIST表，这在性能上是昂贵的。

解决方案

我处理的每个rdbms处理这个的最有效的方法是处理它与外部联接：

  SELECT whatever 
 FROM employee AS e 
 LEFT JOIN emaillist AS em1 ON e.email1 = em1.email 
 LEFT JOIN emaillist AS em2 ON e .email2 = em2.email 
 WHERE em1.id IS NULL 
和em2.id IS NULL 
  

一般来说，我想你会发现，任何将数据库查询放入循环的情况都是，ummm，次优。 ：）

Am mainly looking at improving the performance of the query and also whether be able to solve in a single query for one of my use case explained below:

There are 2 tables involved:

Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6)
Table 2: EMAILLIST (email)

My requirement is, I want to get/fetch all records from the table EMPLOYEE with the condition that either email1 or email2 do not have a matching entry in EMAILLIST table. To put it simply, if either email1 or email2 matches in EMAILLIST table, then those records should be ignored.

In this case, EMPLOYEE.EMAIL1, EMPLOYEE.EMAIL2 and EMAILLIST.EMAIL will always have single email address stored.

We're using PostgreSQL v8.2.3, if it matters.

Any pointers/ideas/logic are appreciated.

UPDATE: Currently, we've implemented in this way: Fetched all records from EMPLOYEE table and stored in a Java object and for each entry (for loop), this in turn checks in EMAILLIST table, which is costly in terms of performance.

解决方案

The most efficient way to handle this for every rdbms I've dealt with is to handle it with outer joins:

SELECT whatever
FROM employee AS e
LEFT JOIN emaillist AS em1 ON e.email1 = em1.email
LEFT JOIN emaillist AS em2 ON e.email2 = em2.email
WHERE em1.id IS NULL
    AND em2.id IS NULL

In general, I think you'll find that any case where you put database queries into a loop will be, ummm, suboptimal. :)

这篇关于我的用例可以合并到单个查询中，而不会影响性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！