Very slow loading a datatable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Malhavoc - 14 Nov 2006 10:11 GMT

I have an access database with a crosstable query in it. Opening that
from Accees takes an eyeblink, but when I try to fill a datatable with
those rows, it takes an handful of seconds; they are more or less 1500
records, and that's not good since my application needs to quickly load
ten times that number.

I've tried using ADO to get a result set and then do:

Dim dtH as New DataTable
Dim da As New System.Data.OleDb.OleDbDataAdapter()
da.Fill(dtH, rs)

..but the fill takes too much

I've also tried this OledDB to get a DataReader and then do:

While rd.Read
rd.GetValues(obj)
dtH.Rows.Add(obj)
End While

..but it takes too much anyway.

Seems that what takes so much time is not the DB call itself, but the
filling of the datatable. However, I don't know if I have alternatives
cause I need to show those rows in a datagridview linked to the above
datatable.

Any suggestion?

Reply to this Message

Grant - 14 Nov 2006 21:04 GMT

ADO.NET is not really designed for displaying very large DataSets. Not
only will it take a long time to load a DataTable with 15000 records
but it will also consume a large amount of memory. You could provide
some means of filtering your displayed data or only loading the first
100 or so matching results.

If you want to be able to browse large datatables however you really
need a connected cursor. Fortunately you can still use traditional ADO
(by just adding a reference to ADODB) from .NET. This allows you to
use connected server side cursors. Unfortunately you can't bind ADODB
recordsets to a DataView. Infralution has a product (Virtual Data
Objects) that allows you to bind .NET controls to ADODB recordsets.
This provides almost instantaneous loading and minimal memory overhead
when browsing very large tables. You can get more information and
download an evaluation version from:

www.infralution.com/virtualdata.html

Regards
Grant Frisken
Infralution

> I have an access database with a crosstable query in it. Opening that
> from Accees takes an eyeblink, but when I try to fill a datatable with
[quoted text clipped - 25 lines]
>
> Any suggestion?

Reply to this Message

msgroup - 15 Nov 2006 13:32 GMT

See the short article at http://www.udaparts.com/devguide.htm

>I have an access database with a crosstable query in it. Opening that
> from Accees takes an eyeblink, but when I try to fill a datatable with
[quoted text clipped - 25 lines]
>
> Any suggestion?

Reply to this Message

Very slow loading a datatable

Free Magazines