编写此查询的最佳方法是什么

I have a table in my database that has 1.1MM records. I have another table in my database that has about 2000 records under the field name, "NAME". What I want to do is do a search from Table 1 using the smaller table and pull the records where they match the smaller tables record. For example Table 1 has First Name, Last Name. Table 2 has Name, I want to find every record in Table 1 that contains any of Table 2 Names in either the first name field or the second name field. I tried just making an access query but my computer just froze. Any thoughts would be appreaciated.

我的数据库中有一个包含1.1MM记录的表。我的数据库中有另一个表,在字段名称“NAME”下有大约2000条记录。我想要做的是使用较小的表从表1中搜索并拉出与较小的表记录匹配的记录。例如,表1具有名字,姓氏。表2有Name,我想查找表1中包含第一个名称字段或第二个名称字段中的任何表2名称的每个记录。我尝试只是进行访问查询,但我的计算机只是冻结了。任何想法都会被贬低。

5 个解决方案

#1

have you considered the following:

你考虑过以下几点:

Select Table1.FirstName, Table1.LastName 
from Table1 
where EXISTS(Select * from Table2 WHERE Name = Table1.FirstName) 
   or EXISTS(Select * from Table2 WHERE Name = Table1.LastName)

I have found before that on large tables this might work better than an inner join.

我之前在大型表上发现这可能比内部联接更好。

#2

Be sure to create indexes on Table1.first_name, Table1.last_name, and Table2.name. They will dramatically speed up your query.

确保在Table1.first_name,Table1.last_name和Table2.name上创建索引。它们将大大加快您的查询速度。

Edit: For Microsoft Access 2007, see CREATE INDEX.

编辑:对于Microsoft Access 2007,请参阅CREATE INDEX。

#3

See above previous notes about indexes, but I believe from your description, you want something like:

请参阅上面有关索引的前面说明,但我相信您的描述,您需要以下内容:

select table1.* from table1 
  inner join 
    table2 on (table1.first_name = table2.name OR table1.last_name = table2.name);

#4

It should go something like this,

它应该是这样的,

Select Table1.FirstName, Table1.LastName 
from Table1 
where Table1.FirstName IN (Select Distinct Name from Table2) 
   or Table1.LastName  IN (Select Distinct Name from Table2)

And there are various other ways to run this same query, i would suggest you see execution plan for each of these queries to find out which one is the fastest. In addition creating indexes on the column which is used in a "where" condition will also speed up the query.

还有其他各种方法来运行同一个查询,我建议您查看每个查询的执行计划,以找出哪一个是最快的。此外,在“where”条件中使用的列上创建索引也将加快查询速度。

#5

i agree with astander. based on my experience, using EXIST instead of IN is a lot faster.

我同意旁观者的意见。根据我的经验,使用EXIST代替IN要快得多。

#1