提高运行时间超过3分钟的asp脚本性能

I use an SQL statement to remove records that exist on another database but this takes a very long time.

我使用SQL语句删除另一个数据库中存在的记录,但这需要很长时间。

Is there any other alternative to the code below that can be faster? Database is Access.

下面的代码还有其他替代方案可以更快吗?数据库是Access。

email_DB.mdb is from where I want to remove the email addresses that exist on the other database (table Newsletter_Subscribers) customers.mdb is the other database (table Customers)

email_DB.mdb是我想从中删除其他数据库中存在的电子邮件地址(表Newsletter_Subscribers)的customers.mdb是其他数据库(表Customers)

SQLRemoveDupes = "DELETE FROM Newsletter_Subscribers WHERE EXISTS (select * from [" & strDBPath & "Customers].Customers " _
      & "where Subscriber_Email = Email or Subscriber_Email = EmailO)"

NewsletterConn = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & strDBPath & "email_DB.mdb"

Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = NewsletterConn
MM_editCmd.CommandText = SQLRemoveDupes
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
Set MM_editCmd = Nothing

EDIT: Tried the SQL below from one of the answers but I keep getting an error when running it:

编辑:从其中一个答案尝试下面的SQL但运行时我一直收到错误:

SQL: DELETE FROM Newsletter_Subscribers WHERE CustID IN (select CustID from [" & strDBPath & "Customers].Customers where Subscriber_Email = Email or Subscriber_Email = EmailO)

SQL:DELETE FROM Newsletter_Subscribers WHERE CustID IN(从[“&strDBPath&”Customers]中选择CustID。订阅者,其中Subscriber_Email = Email或Subscriber_Email = EmailO)

I get a "Too few parameters. Expected 1." error message on the Execute line.

我得到“参数太少。预计1.”执行行上的错误消息。

5 个解决方案

#1

I would use WHERE Subscriber_Email IN (Email, Email0) as the WHERE clause

我会使用WHERE Subscriber_Email IN(Email,Email0)作为WHERE子句

SQLRemoveDupes = "DELETE FROM Newsletter_Subscribers WHERE EXISTS " & _ 
(select * from [" & strDBPath & "Customers].Customers where Subscriber_Email IN (Email, EmailO)"

I have found from experience that using an OR predicate in a WHERE clause can be detrimental in terms of performance because SQL will have to evaluate each clause separately, and it might decide to ignore indexes and use a table scan. Sometime it can be better to split it into two separate statements. (I have to admit I am thinking in terms of SQL Server here, but the same may apply to Access)

我从经验中发现,在WHERE子句中使用OR谓词在性能方面可能是有害的,因为SQL必须单独评估每个子句,并且它可能决定忽略索引并使用表扫描。有时将它分成两个单独的语句会更好。 (我不得不承认我在这里考虑SQL Server,但同样适用于Access)

"DELETE FROM Newsletter_Subscribers WHERE EXISTS " & _ 
    (select * from [" & strDBPath & "Customers].Customers where Subscriber_Email = Email)"

"DELETE FROM Newsletter_Subscribers WHERE EXISTS " & _ 
    (select * from [" & strDBPath & "Customers].Customers where Subscriber_Email = EmailO)"

#2

Assuming there's an ID-column present in the Customers table, the following change in SQL should give better performance:

假设Customers表中存在ID列,SQL中的以下更改应该会提供更好的性能:

"DELETE FROM Newsletter_Subscribers WHERE ID IN (select ID from [" & strDBPath & "Customers].Customers where Subscriber_Email = Email or Subscriber_Email = EmailO)"

“DELETE FROM Newsletter_Subscribers WHERE ID IN(从[”&strDBPath&“Customers]中选择ID。订阅者,其中Subscriber_Email = Email或Subscriber_Email = EmailO)”

PS. The ideal solution (judging from the column names) would be to redesign the tables and code logic of inserting emails in the first place. DS

PS。理想的解决方案(从列名称判断)将是重新设计表格和首先插入电子邮件的代码逻辑。 DS

#3

Try adding an Access Querydef and calling that.

尝试添加Access Querydef并调用它。

#4

It sounds like you do not have an index on the subscriber_enail field. This forces a table scan ( or several). Add an index on this field and you should see significant improvement.

听起来您在subscriber_enail字段上没有索引。这会强制进行表扫描(或几次)。在此字段上添加索引,您应该看到显着的改进。

I would have coded the query

我会编码查询

DELETE FROM Newsletter_Subscribers where (Subscriber_Email = Email or Subscriber_Email = EMail0)

#5

I would try splitting this into two separate statements with separate database connections.

我会尝试将其拆分为两个单独的语句,并使用单独的数据库连接。

First, fetch the list of email addresses or IDs in the first database (as a string).

首先,获取第一个数据库中的电子邮件地址或ID列表(作为字符串)。

Second, construct a WHERE NOT IN statement and run it on the second database.

其次,构造一个WHERE NOT IN语句并在第二个数据库上运行它。

I would imagine this would be much faster as it does not have to interoperate between the two databases. The only possible issue would be if there are thousands of records in the first database and you hit the maximum length of a sql query string (whatever that is).

我想这会快得多,因为它不需要在两个数据库之间进行互操作。唯一可能的问题是,如果第一个数据库中有数千条记录,并且您达到了sql查询字符串的最大长度(无论是什么)。

Here are some useful functions for this:

以下是一些有用的功能:

function GetDelimitedRecordString(sql, recordDelimiter)
    dim rs, str
    set rs = db.execute(sql)
    if rs.eof then
        str = ""
  else
        str = rs.GetString(,,,recordDelimiter)
      str = mid(str, 1, len(str)-len(recordDelimiter))
    end if
    rs.close
    set rs = nothing
    GetDelimitedRecordString = str
end function

function FmtSqlList(commaDelimitedStringOrArray)
    ' converts a string of the format "red, yellow, blue" to "'red', 'yellow', 'blue'"
    ' useful for taking input from an html form post (eg a multi-select box or checkbox group) and using it in a SQL WHERE IN clause
    ' prevents sql injection
    dim result:result = ""
    dim arr, str
    if isArray(commaDelimitedStringOrArray) then
        arr = commaDelimitedStringOrArray
    else
        arr = split(commaDelimitedStringOrArray, ",")
    end if
    for each str in arr
        if result<>"" then result = result & ", "
        result = result & "'" & trim(replace(str&"","'","''")) & "'"
    next
    FmtSqlList = result
end function

#1