Q: slow data adaptor

[Geoff Jones]

Hi

I am loading a DataSet using a DataAdaptor e.g.

myDataAdaptor = New OleDb.OleDbDataAdaptor("Select * From [myTable]",
myConnection)

myDataAdaptor.Fill(myDataSet, "MyTable")

The table is very large and it takes quite a few seconds for these two steps
to complete. I do not need to write back to the table and I was wondering if
using a DataAdaptor may be a bit of overkill. I do need a DataSet to perform
some quite complicated data analysis.

I believe that a DataReader may be a way of speeding things up but I don't
have any idea of how to do the above using it. I'm sure this is a very
simple question so please forgive my ignorance - you may have gathered that
I'm new to all this database stuff!

Thanks in advance

Geoff

 

[Cor Ligthert]

Geoff,

You can maybe gain some seconds however for reading (fill) is the
dataadapter at this moment using the datareader. So that will probably not
speed up your reading.

I have readed in the AdoNet newsgroup that when you use not all columns it
will speed up to specify them, however even from that I am not sure. I never
will use huge datasets.

The slow point is probably your database, by instance ms-access is terrible
slow.

I hope this helps anyhow?

Cor

 

[Geoff Jones]

As always Cor very enlightening

Geoff

 

[Geoff Jones]

On the same topic, maybe somebody can answer a question has been puzzling me
for some time. I have seen on many postings and web sites that it isn't a
good idea to load a DataSet with a large number of records e.g. > 10000.

This begs the question, and forgive me if I'm being naive here, but what is
the alternative? If, as I suspect, it is to use raw SQL to do all the
searches, relationships etc. then why isn't this done for all database work?
Why use a DataSet?

I'd be interested to hear your comments.

Geoff

 

[Cor Ligthert]

Geoff,

Although that has as well alternatives. What when you use PDA's and need all
the data to pull down once every week without that updating is needed. Is
than a huge dataset not a great solution.

However, when you needs two tables and you know from from that what, by
instance let say an order, is than this not much nicer to get the data.

Select * from OrderHeadings where customer = @customerId
Select * from OrderRows where customer = @customerId
(and than keep it simple without any join)

And than process those two tables.

(And than first update the header and than the orderrows)

Just my idea

Cor

 

[Geoff Jones]

Interesting. Thanks Cor

 

[Chef Groovy]

I had to upgrade my Access database to SQL2000 after it got soo big. The
lookups just got too slow for production. Seems access was slow even
with 100k records or so. MSSQL is almost instant. Money well spent.

And the beauty was that converting code to work with sql was pretty much
just changing the connection string, everything else just fell into
place.

Hi

I am loading a DataSet using a DataAdaptor e.g.

myDataAdaptor = New OleDb.OleDbDataAdaptor("Select * From [myTable]",
myConnection)

myDataAdaptor.Fill(myDataSet, "MyTable")

The table is very large and it takes quite a few seconds for these two
steps to complete. I do not need to write back to the table and I was
wondering if using a DataAdaptor may be a bit of overkill. I do need a
DataSet to perform some quite complicated data analysis.

I believe that a DataReader may be a way of speeding things up but I
don't have any idea of how to do the above using it. I'm sure this is
a very simple question so please forgive my ignorance - you may have
gathered that I'm new to all this database stuff!

Thanks in advance

Geoff