使用vb.net更新访问表非常慢 [英] Very slow updating access table using vb.net

问题描述

嘿大家，我在这里遇到一些问题。我有一个大的Access .mdb（超过400,000行）。


我需要做的是，有12列需要重新排序（非常简单，如果它是XZY，更改为XYZ，但旁边的行必须也可以相应地改变，如WEC到WCE [换句话说，但是第一行的顺序改变了，第二行也必须以完全相同的顺序改变]。


无论如何，当计算具有上述错误的所有行时，有近300,000行受到影响。我需要编写一个可以通过的快速程序或Web应用程序，找到任何未正确排序的行（XYZ）并将其更改（及其旁边的行）到正确的顺序。然后我需要使用新数据更新Access数据库。


这是我当前的代码：

展开 | 选择 | Wrap | 行号

解决方案

这是只需要发生一次的事情吗？

一旦事情顺利完成你就不必担心吗？

在很慢的时候把它关掉然后放手。

我已经尝试过这个过夜了，它只完成了大约30％的它。这需要大约3整天才能完成。如果我要让这个周末运行，如果出现问题，我直到周一都不会知道，这可能意味着我必须重新开始。


我需要能够尽快完成这项工作的东西，最好是几个小时，不超过一天（这样我就可以开启和关闭它24小时）。

我有一个想法是将其导入SQL Server数据库，然后在那里进行更新（使用ASP Web应用程序或.net应用程序），然后将其导出回Access数据库。这是一个可行的解决方案还是我会遇到相同的速度问题？


MGM out

让我看看我是否理解这个问题/>
您有一个类似的表：


A | B | C $ / $
------------

1 | 3 | 2

4 | 3 | 6


例如。而且你需要沿着每一行去修正它们以进行一些设计。

（我的例子我会让它们计算在内）

A | B | C $ / $
------------

1 | 2 | 3

3 | 4 | 6


这是对的吗？因为哦，男孩会非常密集。

您可以尝试将其移至MSSQL，但我不知道有多少时间会真正购买你。

您可能只想尝试逐节进行操作。一次只能处理50k条目的大块。

如果不需要修复，我会认为你的时间会大大减少。


所以如果你只是继续做50k，直到你认为你已经完成所有这些，那么对整个事情进行扫描，全扫描应该花费更少的时间，因为希望它不需要修复任何。

另外，对于50k条目，你只会试图移动一小部分数据而增加额外的好处

Hey everyone, I''m having some issues here. I have a large Access .mdb (over 400,000 rows).

What I need to do is, there are 12 columns that need to be reordered (quite literally as simple as if it is XZY, change to XYZ, but the row next to it must ALSO be changed accordingly, as in, WEC to WCE [in other words, however the first row''s order was changed, the second row must also be changed in the exact same order]).

Anyways, when counting all of the rows that have the above mistake, there are nearly 300,000 rows affected. I need to write a quick program or web app that can go through, find any row that isn''t ordered properly (XYZ) and change it (and the row next to it) to the proper order. Then I need to update the Access database with the new data.

Here is my current code:

Expand|Select|Wrap|Line Numbers

解决方案

this is something that only needs to happen once though right?
once things are in order you don''t have to worry about it?
fire it off on a slow time and let it go.

I already tried having this run overnight and it only completed about 30% of it. It would take about 3 full days for this to get done. If I were to let this run over the weekend, and if something were to go wrong, I wouldn''t know about it until Monday, which probably means I''d have to start it all over again.

I need something that can get this done as quickly as possible, preferably a few hours, and no more than a day (so that I can keep track of it on and off over a 24 hour period).

One idea I had is to import this into a SQL Server database, then do the updating there (using an ASP web app or .net app), and then export it back to an Access database. Is this a viable solution or will I run into the same speed issues?

MGM out

So let me see if I understand this
You have a table like:

A | B | C
------------
1 | 3 | 2
4 | 3 | 6

For example. And you need to go down each row and correct them as to some design.
(for my example I will make them count up)
A | B | C
------------
1 | 2 | 3
3 | 4 | 6

Is that correct? Cause oh-boy would that ever be intensive.
You could try moving it to MSSQL but I don''t know how much time that will really even buy you.
You may want to just try and do it section by section. Do it in chunks of 50k entries at a time or something.
If they don''t need to be fixed, your time will be greatly reducee I would think.

So If you just keep doing 50k until you think you''ve gotten them all, then do a scan over the WHOLE thing, the full scan should take way less time because hopefully it won''t have to fix any.
Plus, with the 50k entries you''ll have the added bonus of only trying to move around a much smaller chunk of data

这篇关于使用vb.net更新访问表非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！