VB.NET Datatable永远加载 [英] VB.NET Datatable loads forever

问题描述

大家好



我正在使用SQL Server 2008 Express开发项目。

我的项目是现有程序的较新版本我们之前使用Microsoft Access作为数据库开发。



我现在要做的是写一个数据转换程序，这样我就可以将旧数据（Access）放入新数据库（SQL）。需要进行一些修改，因此无法支持SQL。



到目前为止，我正在创建与de Access数据库的连接，编写将获得的SQL命令我想从Access数据库中获取数据，然后使用该查询的结果填充数据表。



当我填充数据表时，我可以随心所欲地做任何事情。将数据插入SQL之前的数据。



我现在面临的问题是我必须加载一张包含130.000条记录的表。我尝试了两种不同的形式：



方法1

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

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

方法2

method 2

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

这两个功能都需要永远执行步骤dt.Load（读卡器）或adapter.Fill（dt）。



有没有人有任何id为什么这不起作用？我让程序运行了几个小时但没有结果...



我希望检索的数据是直接的，所以没有内部或外部连接，只是简单的数据从1表....

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....

推荐答案

为什么关闭连接，如果几行关闭，连接打开？

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

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

请参考： OleDbCommand Class [ ^ ]并查看代码sampl e在页面底部。

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

130,000行可以一次加载：我假设你不是想直接向用户提供这个行！



你可以采取一些措施来加快速度，但是如果有这么多行，它们可能不会产生重大影响 - 但它们很简单。首先查看您的查询。您是否正在检索不需要的信息？例如，130,000个100个字符的文本字段每个需要13,000,000字节的带宽才能传输，图像字段将更大。如果你的查询是

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

SELECT * FROM MyTable

然后你可以通过仅获取你需要的数据来节省大量时间。



还有on选项-demandloading - 你一次获取所需的最小数据（例如行ID），然后只在实际需要时获取行详细信息。我使用这个系统来获得良好的效果：按需加载来自数据库的图像 [ ^ ]（它在C＃中，但很明显发生了什么）



最大的性能提升（来自用户透视图）是最难实现的：将加载代码移动到一个单独的线程中并逐行检索，根据需要进行更新。实际执行此操作并不太复杂 - BackgroundWorker [ ^ ]非常简单 - 但它可能意味着对其他代码的财务更改，具体取决于您使用的方式。我倾向于尝试在启动时在后台加载当用户实际上没有做任何事情，如果可以的话。

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.

建议



1.谁会在页面中看到13K的记录？因此，将查询限制为返回少量记录。

2.始终在select查询中提及列名。选择*将返回所有列，但可能不需要所有列。

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.

这篇关于VB.NET Datatable永远加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！