VB.net Large SQL Database Out of memory error

Question

0.00/5 (No votes)

See more:

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)

VB

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

Posted 27-Jan-16 15:05pm

bayotle

Add a Solution

Comments

barneyman 27-Jan-16 21:19pm

you're trying to populate the DA with the entire rowset ...

try using Fill paging

bayotle 27-Jan-16 22:19pm

So Coding error, or dfp (dumb f'n programmer!)

_Asif_ 28-Jan-16 1:28am

Another approach might be to do the changes at database level, like creating a Stored Procedure in which you can use SELECT INTO clause that can create new table with new columns as well in 1 go

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2016-01-27T15:26:00

Solution 1

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.

Posted 27-Jan-16 15:26pm

Dave Kreskowiak

Comments

bayotle 27-Jan-16 22:22pm

Thanks Dave!
Being a newb that wasn't in my arsenal but now that I see the code for it, makes more sense.
I knew the fill was loading the enter table but didn't know another way, maybe this will keep me from cursing .net having such slow db access! ;)

VB.net Large SQL Database Out of memory error

1 solution

Solution 1

Add your solution here

Preview 0

VB.net Large SQL Database Out of memory error

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us