VB.net大型SQL数据库内存不足错误 [英] VB.net Large SQL Database Out of memory error

问题描述

我有一个大型SQL表，其中有近700万条记录，没有唯一字段。

我需要在多个字段中重新创建表中的附加信息，这样就可以获得独特的信息，因此它可以可以访问。



计划是循环整个表并使用附加信息更新另一个表但是在尝试填充第一个数据时出现内存不足错误适配器......



比需要修复更好奇，不确定这是我在.net中的db编码的问题，还是这是.net的另一个限制...



任何指针都将不胜感激！



（以下部分代码）

I have a LARGE SQL table with almost 7 million records with no unique field.
I need to 'recreate' the table with additional information in multiple fields which will give it unique information so it can be accessed.

The plan was to loop the entire table and update another table with the additional information but I get an out of memory error when trying to fill the first data adapter...

More curious than needing a fix, not sure if this is an issue with my db coding in .net or if this is another limitation of .net...

Any pointers would be appreciated!

(Partial code below)

Dim SQL As String = "Select * from db"
Dim Conn As New SqlConnection
Dim myDataAdapter As New SqlDataAdapter
Dim MyDataSet As New DataSet
Try
    Conn.ConnectionString = "Server=xxx;Database=xxx;Trusted_Connection=True;"
    Conn.Open()
    myDataAdapter = New SqlDataAdapter(SQL, Conn)
    myDataAdapter.Fill(MyDataSet, "db") <-- memory error here

推荐答案

请勿使用DataAdapter和Fill来执行此操作。您正在将ENTIRE表加载到内存中，而您实际上无法使用700万条记录。



使用SqlDataReader而不是一次读取一条记录，处理它并使用新数据更新您的其他表。您需要将数据库的连接分开，因为您无法使用与其上的活动DataReader的连接来执行其他操作。

Do NOT use a DataAdapter and Fill to do this. You're loading the ENTIRE table into memory and you really cannot do that with 7 million records.

Use a SqlDataReader instead and read one record at a time, process it and update your other table with the new data. You'll need to separate connections to the database as you cannot use a connection with an active DataReader on it to do other operations.

这篇关于VB.net大型SQL数据库内存不足错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！