首页 文章

从Access前端通过WAN更新SQL Server表非常慢

提问于
浏览 次
0

我有一个托管在我们的共享主机帐户上的小型数据库，我通过具有链接SQL表的访问MDE文件进行更新，该文件每天多次通过任务调度程序运行 .

基本上有一个小的（在20行以下）用户表和一个（当前）1000行数据表 .

基本上我的MDE'前端'所做的是将我的 生产环境 数据库中的数据查询到Access中的临时表，从我的托管提供商的两个远程表中删除数据并重新填充它们 . 所以它链接到两个服务器（一个本地和一个远程），我有时读过这可能有问题吗？

随着越来越多的人要求访问他们的数据，数据正在缓慢增长，目前，使用链接表删除/重新填充大约需要5分钟 .

通过使用SQL Passthrough删除远程表数据，我减轻了部分缓慢 . 删除几乎是即时的 .

但是，我不能对APPEND做同样的事情，因为我的临时数据仅在本地MDE文件上并且上传它没有任何意义（它不会比现在更快） .

我想知道的是如何加快APPEND查询到远程服务器的速度？当你将它放入Excel（几百KB，MAYBE）时，1000行数据（类似于8列，几乎没有空值）并不是很大，所以采用这么长的APPEND是没有意义的 .

User和Datatable都有一个时间戳数据列，我读过这有助于加快Access - > SQL delete / appends，因为Access可以检查时间戳与逐行比较 .

我会喜欢一些关于如何提高速度的建议 . 无论如何，这不是大量的数据 .

Private Sub UpdateWebFunc()
On Error GoTo errors
   Dim conn As New ADODB.Connection
   Dim cmd As New ADODB.Command
   Dim rst As New ADODB.Recordset
   Dim rst2 As DAO.Recordset
   Dim qdf As DAO.QueryDef

   conn.ConnectionString = "Blah blah blah here"

   conn.Open

   Set qdf = CurrentDb.QueryDefs("GetWebData")

   Set rst2 = qdf.OpenRecordset
   If Not rst2.BOF And Not rst2.EOF Then
   rst2.MoveFirst
   End If

   With cmd
    .CommandText = ”sp_UpdateWeb”
    .CommandType = adCmdStoredProc
    .ActiveConnection = conn
    .NamedParameters = True
    '.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
    .Parameters.Append .CreateParameter("@SampID", adVarChar, adParamInput, 50)
    .Parameters.Append .CreateParameter("@ReportTitle", adVarChar, adParamInput, 50)
   End With
   conn.BeginTrans
   Do Until rst2.EOF
    cmd.Parameters("@SampID") = rst2!SampID
    cmd.Parameters("@ReportTitle") = rst2!ReportTitle

    cmd.Execute , , adExecuteNoRecords
   rst2.MoveNext
   Loop
   conn.CommitTrans
   conn.Close
Set cmd = Nothing

   rst2.Close
   Set rst2 = Nothing

errors:
   MsgBox "Number: " & Err.Number & vbCr & " Description: " & Err.Description
End Sub

我一直卡在cmd.execute上，语法错误 . 我试图将其缩小到表中两个最重要的字段，以消除其他冲突 .

ERROR: [Microsoft][ODBC SQL Server Driver]Syntax Error or access violation

这是我的SP：

USE [MYDB]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_UpdateWeb] 
    -- Add the parameters for the stored procedure here
    @SampID VarChar(50),
    @ReportTitle VarChar(50)

AS
BEGIN
    -- Insert statements for procedure here
    INSERT INTO [dbo].[WEB] (SampID, ReportTitle) VALUES (@SampID, @ReportTitle)

END
sql sql-server ms-access

2 回答

  • 1

    为什么不将远程服务器作为链接服务器附加到 生产环境 服务器，并完全绕过Access？我喜欢Access，但在这种情况下，它是不必要的，并且直接将允许您使用SQL代理来自动执行任务 .

    回复于
  • 2

    我认为你现在使用访问查询附加数据？您可能尝试使用暴力方法并在代码中打开本地表并循环遍历该记录集，触发每行的insert语句 . 您可以将此全部包装在事务中以提高性能，您还可以尝试在调用的SQL服务器上创建存储过程并传递参数，而不是在本地创建insert语句 .

    代码示例已添加*

    您已经知道如何打开本地表，因此我将使用presto代码 . 你可以（有些人认为应该）使用DAO作为本地访问表 . 然后在这样的ADO连接上触发存储过程

    DIM cmd as NEW ADODB.Command
Dim dbCon as NEW ADODB.Connection

dbCon.ConnectionString = “Your connection string to the SQL server”
dbCon.Open

‘Setup the command
With CMD
 .CommandText=”spYour_stored_procedure”
 .CommandType=adCmdStoredProc
 .ActiveConnection=dbCon
 .NamedParameters=True
 .Parameters.Append .CreateParameter("@YourIntField", adInteger, adParamInput, 0, 123456)
 .Parameters.Append .CreateParameter("@YourTextField", adVarChar, adParamInput, 20, “Badger”)
 ‘keep on adding parameters
End With
dbcon.BeginTrans
Do until YourDAORecordset.EOF
 cmd.Parameters(“@YourIntField”)= YourDAORecordset.YourField
 cmd.Parameters(“@YourTextField”)=” YourDAORecordset.YourOtherField
 cmd.Execute , , adExecuteNoRecords
 YourDAORecordset.MoveNext
Loop
dbcon.CommitTrans
Dbcon.close
Set cmd=nothing
    回复于

相关问题