VB.NET Datatable loads forever

Question

0.00/5 (No votes)

See more:

, +

Hi guys

I am developing a project using SQL server 2008 Express.
My project is a newer version of an existing program we developed earlier using Microsoft Access as a database.

What I want to do now is write a data conversion program so I can put the old data (Access) in the new datatabse (SQL). Some modifications are needed so no ipport to SQL is possible.

What I am doing so far is creating a connection to de Access database, writing an SQL command that will get the datat I want from the Access database and then fill a datatable with the result of that query.

When I have the datatable filled I can do whatever I want with the data before I insert it into SQL.

The problem that I am facing now is that I have to load a table with 130.000 records. I tried this in two different forms:

method 1

VB

Public Function ReadAccessTableContent(ByVal sqlq As String, ByVal db As String) As DataTable

       set_Access_Connection(db)
       Dim oledbcon As New OleDbConnection(strAccCon)
       Dim myOLEDBCommand As New OleDbCommand(sqlq, oledbcon)
       Dim dt As New DataTable

       dt.Clear()

       If oledbcon.State = ConnectionState.Open Then
           oledbcon.Close()
       End If

       oledbcon.Open()

       Using reader As OleDbDataReader = myOLEDBCommand.ExecuteReader((CommandBehavior.CloseConnection))
           Try
               dt.Load(reader)
           Catch ex As Exception
               MessageBox.Show(ex.Message)
           End Try
       End Using

       Return dt

   End Function

method 2

VB

Public Function ReadAccessTableContent2(ByVal sqlq As String, ByVal db As String) As DataTable

    set_Access_Connection(db)
    Dim oledbcon As New OleDbConnection(strAccCon)
    Dim myOLEDBCommand As New OleDbCommand(sqlq, oledbcon)
    Dim dt As New DataTable

    'dt.Clear()

    If oledbcon.State = ConnectionState.Open Then
        oledbcon.Close()
    End If

    oledbcon.Open()

    Dim adapter As New OleDbDataAdapter(myOLEDBCommand)
    adapter.Fill(dt)

    Return dt

End Function

Both of these functions just take forever to execute on the step dt.Load(reader) or adapter.Fill(dt).

Does anyone have any idea why this will not work? I let the program run for multiple hours but no results...

The data I wish to retrieve is straight forward, so no inner or outer joins, just simple data from 1 table....

Posted 18-Dec-13 5:12am

davy.lambrechts

Updated 18-Dec-13 5:55am

Maciej Los

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-12-18T05:58:00

Solution 2

Why to close connection, if a few lines down, connection is opened?

VB

If oledbcon.State = ConnectionState.Open Then
    oledbcon.Close()
End If
'here!
oledbcon.Open()

Please refer this: OleDbCommand Class[^] and have a look at code sample at the bottom of page.

Posted 18-Dec-13 5:58am

Maciej Los

Updated 18-Dec-13 6:08am

v2

Comments

davy.lambrechts 18-Dec-13 18:11pm

I wrote the if because I want to check if the connection is open or closed because I need this connection for other purposes also. If I try to open the connection when already opened you get an error.

Maciej Los 18-Dec-13 18:14pm

Then check if it's closed ;)

OriginalGriff · Answer 2 · 2013-12-18T05:29:00

130,000 rows is a lot to load in one go: I assume that you aren't trying to present this to the user directly!

There are a couple of things you can do to speed it up, but with that number of rows they probably aren't going to have a major effect - but they are simple. Start by looking at your query. Are you retrieving information you don't need? For example, 130,000 text fields of 100 characters each needs 13,000,000 bytes of bandwidth just to transfer, and an image field is going to be even bigger. If your query is

SQL

SELECT * FROM MyTable

Then you may be able to save significant time by fetching only the data you need.

There is also the option of "on-demand" loading - you fetch the minimum data necessary in one go (just the row IDs for example) and then only fetch the row detail when you actually need it. I use this system for images to good effect: On-demand loading of images from a database[^] (it's in C#, but it's pretty obvious what is going on)

The biggest performance improvement (from a user perspective) is the hardest to implement: move your loading code into a separate thread and retrieve row-by-row, updating as needed. It's not too complex to actually do this - a BackgroundWorker[^] is pretty simple - but it can mean substancial changes to other code, depending on how you are using things. I tend to try to load in the background at startup when the user isn't actually doing anything yet, if I can.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 3 · 2013-12-18T07:10:00

Solution 3

Suggestions

1. Who is going to see 13K records in a Page? So, limit your query to to return a small number of records.
2. Always mention the column names in the select query. Selecting by * would return all columns, but all of them might not be required.
3. As soon as the data are retrieved, close and dispose the database connections.

Posted 18-Dec-13 7:10am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

davy.lambrechts 18-Dec-13 18:11pm

Hi guys, thanks for the responses.

My query already consists of the fields I need, so no 'select * from' is used.
I also don't want to show 130.000 rows to a user. The thing I'm trying to do is get the data from a table in Access, put it in a datatable and then use this datatable to bulkcopy in an SQL server database table.

As soon as the data is received: that's the problem, I get stuck on the 'receive data' part...

thatraja · Answer 4 · 2013-12-18T14:00:00

Solution 4

Use approach like SqlBulkCopy[^]. And here a comparison
High performance bulk loading to SQL Server using SqlBulkCopy[^]

Posted 18-Dec-13 14:00pm

thatraja

Comments

davy.lambrechts 19-Dec-13 3:18am

Thanks for the help, but I'm already using the SQLBulkCopy command to insert my data into SQL Server.

This is not the problem. My problem occurs in the steps before, when I want to load my data into a .NET datatable so I can pass it to the SQLBulkCOpy command.

The command datatable.load(datareader) (where I want to load the Access table data using a select statement) takes all night to load a simple 130.000 records...

VB.NET Datatable loads forever

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us