如何快速从数据库加载100万条记录？

发布于 2024-08-29 22:59:51 字数 131 浏览 4 评论 0原文

现在我们有一个包含 1.000.000 的 firebird 数据库，必须在将所有内容加载到 RAM 内存后对其进行处理。为了获得所有这些，我们必须使用 (select * first 1000 ...) 提取数据 8 小时。对此有什么解决办法吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乱世争霸 2024-09-05 22:59:51

您的每个“select * first 1000”（如您所描述的）是否都会进行全表扫描？查看这些查询，并确保它们正在使用索引。

回复收藏 0 原文

疯了 2024-09-05 22:59:51

构造每次读取数据时创建的 DTO 对象需要多长时间？

{ int a = read.GetInt32(0); int b = read.GetInt32(1); mylist.Add(new DTO(a,b)); }

您正在创建一百万个这样的对象。如果创建一个 DTO 对象需要 29 毫秒，那么这将需要 8 个多小时才能完成。

How long does it take to construct the DTO object that you are creating with each data read?

{ int a = read.GetInt32(0); int b = read.GetInt32(1); mylist.Add(new DTO(a,b)); }

You are creating a million of these objects. If it takes 29 milliseconds to create one DTO object, then that is going to take over 8 hours to complete.

回复收藏 0 原文

会发光的星星闪亮亮i 2024-09-05 22:59:51

从表中加载数据
使用 firebird 数据库的 C# 中的 1.000.000 行至少需要 Pentium 4 3Ghz
8小时

每个人都假设您正在运行 SQL 查询来从数据库中选择记录之类的东西，

select * 
from your_big_table
/

因为这确实需要几秒钟。嗯，在屏幕上显示它的时间要长一点，但执行实际的选择应该快如闪电。

但对 C# 的引用让我认为您正在做其他事情。也许您真正拥有的是实例化一百万个对象的 RBAR 循环。我可以想象这可能需要更长的时间。但即便如此，八小时？时间都去哪儿了？

编辑

我的猜测是正确的，您正在循环中实例化 1000000 个对象。正确的建议是，一旦将所有对象都存储在内存中，就找到其他方法来完成您要做的事情。在不了解更多细节的情况下，很难给出具体细节。但这似乎不太可能是用户界面的想法——哪个用户会仔细阅读一百万个对象？

因此，一般观察就足够了：使用批量操作来实现批量活动。 SQL 数据库擅长处理集合。利用 SQL 的强大功能来处理单个集合中的百万行，而不是作为单独的行。

如果您认为此答案没有帮助，那么您需要向我们提供有关您想要实现的愿望的更多详细信息。

to load data from a table with
1.000.000 rows in C# using a firebird db takes on a Pentium 4 3Ghz at least
8 hours

Everybody's been assuming you were running a SQL query to select the records from the database Something like

select * 
from your_big_table
/

Because that really would take a few seconds. Well, a little longer to display it on a screen, but executing the actual select should be lightning fast.

But that reference to C# makes me think you're doing something else. Perhaps what you really have is an RBAR loop instantiating one million objects. I can see how that might take a little longer. But even so, eight hours? Where does the time go?

edit

My guess was right and you are instantiating 1000000 objects in a loop. The correct advice would be to find some other way of doing whatever it is you do once you have got all your objects in memory. Without knowing more about the details it is hard to give specifics. But it seems unlikely this is a UI think - what user is going to peruse a million objects?

So a general observation will have to suffice: use bulk operations to implement bulk activity. SQL databases excel at handling sets. Leverage the power of SQL to process your million rows in a single set, rather than as individual rows.

If you don't find this answer helpful then you need to give us more details regarding want you're trying to achieve.

回复收藏 0 原文