循环的.Refresh导致Excel运行缓慢。

huangapple go评论83阅读模式
英文:

Looped .Refresh causing slow Excel

问题

我正在尝试在Excel for Mac中使用VBA来查询数据库。一个简单的查询运行正常,我已经使用它多年了。现在我正在进行更复杂的查询,其中一个查询的结果(约1,000条记录)被顺序地用于再次查询数据库(因此大约有1,000次连续查询)。结果(约5,000条记录)大约在2.5分钟内返回,但在之后的一分钟内,单击Excel工作表不响应。这种行为会持续大约2分钟,然后单击几乎立即变得响应。再次运行宏会产生类似的结果,但更慢。第三次更慢。我怀疑是内存泄漏。重新启动Excel会解决问题。以下是实际查询的代码:

With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
    .PostText = "user=" & strUserName & ";password=" & strUserPassword
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .BackgroundQuery = False
    .Refresh
End With

我尝试只发送第一个查询(以获取其他查询的数据)。其余的查询我构建了查询,但没有发送。因此,在这个实验中,上述代码仅使用了一次。以这种方式运行,它大约在8秒内返回。所以其他2分20秒左右来回传递我的计算机和数据库之间的数据。更重要的是,在以这种方式运行后,运行完成后没有延迟。所以如果存在内存泄漏,似乎泄漏发生在查询过程中,或者可能是数据的实际写入。

我已经在开头通过编程方式关闭了所有的屏幕更新、分页显示和计算,并在最后恢复了原始设置。

我的计算机是Mac mini(2014年底)3GHz英特尔Core i7,配备Office 2011,但我也尝试在新款M1上运行,使用最新版本的Excel。它速度快多了,但结果返回后的延迟,虽然较短,仍然是个问题。我的计算机代表了近期将运行电子表格的地方。

之后的延迟真的会影响项目的这一部分。有人以前见过这个问题吗?有没有办法追踪是什么导致了这个问题,以及是否有办法解决它?

英文:

I am trying to use VBA in Excel for Mac to query a database. A simple query works fine and I've been using it for years. Now I'm getting into more complex queries, where the results of one query (about 1,000 records) are used sequentially to query the database again (so about 1,000 consecutive queries). The results (about 5,000 records) return in about 2.5 minutes, but clicking in the Excel sheet is not responsive for about a minute afterward. This behavior continues for another 2 minutes or so before clicking becomes pretty much instantaneous. Running the macro again gives similar results but slower. The 3rd time is even slower. I suspect a memory leak. Restarting Excel makes the problem reset. Here is the code for the actual query:

    With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
        .PostText = "user=" & strUserName & ";password=" & strUserPassword
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .BackgroundQuery = False
        .Refresh
    End With

I've tried to actually send just the first query (so it gets data from which the other queries can be built). The rest of them, I build the query, but don't send it. So, in this experiment, the above code got used only once. Running it this way, it comes back in about 8 seconds. So the other 2 minutes and 20+ seconds are back and forth between my computer and the database. More importantly, after running this way, there is no lag after it is done running. So it seems like if it is a memory leak, the leak is in the query process, or maybe the actual writing of the data.

I have programmatically turned off all of the screen updating, page break showing, and calculating at the beginning and returned them to the original settings at the end.

My computer is a Mac mini (Late 2014) 3GHz Intel Core i7 with Office 2011, but I've tried running it on a newer M1 with the newest version of Excel also. It was much faster, but the lag after the results were returned, though shorter, was still a problem. My computer is representative of where the spreadsheet will be run for the near future.

The lag afterwards is really going to kill this part of the project. Has anybody seen this problem before? Is there something I can do to trace what is causing the problem and if there is a way around it?

答案1

得分: 1

ChatGPT解决了我的核心问题。它指出我为我的1000多个查询创建了单独的查询表,这些查询表占用了我的内存,导致我的应用程序变得缓慢。它说,如果我在With...End With块后面添加.Delete行,它将清除以前的查询表,不会占用所有内存。答案略有不准确,.Delete需要放在块内(在末尾)。不管怎样,我尝试了这个方法,虽然代码运行起来有点慢,但运行结束后应用程序不再缓慢。对我来说,这是一个胜利。

它还进一步建议使用ADO可能会更好地完成这个任务。所以我想这对其他遇到类似问题的人可能会有用。

另一个编辑:我正在使用Mac,所以ADO对我来说不适用。

以下是有效的查询:

With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
        .PostText = "user=" & strUserName & ";password=" & strUserPassword
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .BackgroundQuery = False
        .Refresh
        .Delete
End With
英文:

ChatGPT solved the crux of my issue. It pointed out that I'm creating a separate Query Table for each of my 1000+ queries and those are eating up my memory, causing my application to become slow. It said that if I add a .Delete line after the With... End With block it would wipe out the previous Query Table and not use up all that memory. The answer was slightly inaccurate. The .Delete needed to go inside the block (at the end). Anyhow, I tried that and it made my code somewhat slower, but when it was done, the application was not slow. That is a win in my book.

It further suggested that ADO might do a better job at this task. So I guess that is something else to learn. Thought this might be useful for someone else experiencing a similar issue.

another edit: I'm working on a Mac, so ADO will not do the trick for me.

Here was the working query:

With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
        .PostText = "user=" & strUserName & ";password=" & strUserPassword
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .BackgroundQuery = False
        .Refresh
        .Delete
End With 



</details>



huangapple
  • 本文由 发表于 2023年2月10日 03:47:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/75403723.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定