Slow DB Access with Many Records?

[Burning]

In the simple example that all tutorials give us we do something

like this, eh?:

 

           Dim cn As New OleDb.OleDbConnection
          cn.ConnectionString = "Provider=PervasiveOLEDB;Data Source=C:\Pastel04\SAJ2004"
           cn.Open()

           Dim cmd As New OleDb.OleDbCommand(txtSQL.Text, cn)
           Dim da As New OleDb.OleDbDataAdapter(cmd)
           Dim ds As New DataSet

           ds.Clear()
           da.Fill(ds)

           C1TrueDBGrid1.DataSource = ds.Tables.Item(0)

 

 

but when I reach about 20k records it starts getting reaaaaaallllllyyyyy sloooo

ooooooowwwwwww..... And it happens right here !!!

 

da.Fill(ds)

 

Surely someone else must have noticed this? Can anyone point me

to some work around so it loads the data dynamically, say 20 records

at a time? I know there must be some resource out there that helps

in this area... It's just, that i dunno what to put in Google to find...

 

PLease help !!

Thanx in Advance

James Laker

[Joe Mamma]

In the simple example that all tutorials give us we do something

like this, eh?:

 

 Dim cn As New OleDb.OleDbConnection
cn.ConnectionString = "Provider=PervasiveOLEDB;Data Source=C:\Pastel04\SAJ2004"
cn.Open()

Dim cmd As New OleDb.OleDbCommand(txtSQL.Text, cn)
Dim da As New OleDb.OleDbDataAdapter(cmd)
Dim ds As New DataSet

ds.Clear()
da.Fill(ds)

C1TrueDBGrid1.DataSource = ds.Tables.Item(0)

 

 

but when I reach about 20k records it starts getting reaaaaaallllllyyyyy sloooo

ooooooowwwwwww..... And it happens right here !!!

 

da.Fill(ds)

 

Surely someone else must have noticed this? Can anyone point me

to some work around so it loads the data dynamically, say 20 records

at a time? I know there must be some resource out there that helps

in this area... It's just, that i dunno what to put in Google to find...

 

PLease help !!

Thanx in Advance

James Laker

OMG!!! Pervasive DB!!! Let me know if you have any contract work available. I'm proficient in their SDK and their components.

Pervasive is a Damn good RDBMS for the money!

I think its US$5000 for unlimited seats?

And they know what they are doing - lots of experience there!

Unfortunately their OLEDB does not work on non local DB servers. . .

 

I assume the slowdown happens at da.fill(ds)

Try using:

 

DBAddapter Fill( _

ByVal dataSet As DataSet, ByVal startRecord As Integer, _

ByVal maxRecords As Integer, _

ByVal srcTable As String _

) As Integer

 

Although this will require you to do some row number bookkeeping.

I am interested in whether this works or not, so let me know!

 

Joe Mamma

[Nerseus]

You might also try a DataReader and build your DataSet "by hand". The DataSet has no "paging" or "asynchronous" methods built in, so you get stuck downloading all 20,000 records at once.

 

The BIG question should be, why download all 20,000 records? Maybe download the top 100 or 500 then alerting the user that they need to filter to get better results OR require a filter up front (and still only show the top 100 or 500).

 

No one is really going to page through 20,000 records. The key is to figure out what they really want to do with that data and give them options to get to what they're after. For example, if they want to drill down, then give them a filtered list of "something" from which to drill down on.

 

-ner

[Joe Mamma]

You might also try a DataReader and build your DataSet "by hand". The DataSet has no "paging" or "asynchronous" methods built in, so you get stuck downloading all 20,000 records at once.

 

The BIG question should be, why download all 20,000 records? Maybe download the top 100 or 500 then alerting the user that they need to filter to get better results OR require a filter up front (and still only show the top 100 or 500).

 

No one is really going to page through 20,000 records. The key is to figure out what they really want to do with that data and give them options to get to what they're after. For example, if they want to drill down, then give them a filtered list of "something" from which to drill down on.

 

-ner

You know, I am looking for some indepth internal description of the datareader, dataadapter and dataset components. any recommendations?

[Robby]

Joe, you may already be aware on much of this, it may still be worth a look...http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data010112001.asp

[Robby]

James, Another way is to handle your paging in a stored proc, but I would follow Nerseus' advice on not returning all the records to the client in the first place.

Perhaps by default return top n sorted by date or whatever then allow the user to drill down.