SQL查询的工作速度比Access查询向导慢

I am working on automation of elimination of unmatched rows in two tables in MS Access. As known, Access has a query wizard for this process which named Find unmatched records. This method works fast even for 500k data rows.

我正在努力自动消除MS Access中两个表中不匹配的行。众所周知，Access具有此进程的查询向导，该向导名为“查找不匹配的记录”。即使对于500k数据行，此方法也能快速工作。

But when I execute a query in MS Access VBA it works so slowly. Is there a faster SQL implementation to eliminate data, or does MS Access use a different method? How can I make it fast?

但是当我在MS Access VBA中执行查询时，它的工作速度非常慢。是否有更快的SQL实现来消除数据，或者MS Access是否使用不同的方法？我怎样才能快速完成？

Below is my query in VBA. Table1 and Table2 each have more than 100k rows.

以下是我在VBA中的查询。表1和表2各有超过100k行。

strQuery = SELECT gsmno INTO newtablename FROM table1 WHERE gsmno NOT IN (SELECT gsmno FROM table2)
CurrentDb.Execute strQuery

1 个解决方案

#1

Use a LEFT OUTER JOIN and check for NULL on the Table2 gsmno. Those are your Non-Matches.

使用LEFT OUTER JOIN并在Table2 gsmno上检查NULL。那些是你的不匹配。

strQuery = & _
"SELECT " & _
"   t1.gsmno " & _
"INTO " & _ 
"   newtablename " & _ 
"FROM " & _ 
"   table1 as t1 " & _
"LEFT OUTER JOIN " & _ 
"   table2 as t2 on " & _
"      t1.gsmno = t2.gsmno " & _ 
"WHERE " & _ 
"   isnull(t2.gsmno) = true;"

CurrentDb.Execute strQuery