performance tableadapter access update


J

John

Hi

I am working with an access database with more than 25000 records
(adresses).
In vb .net i use the tableadapter and it takes a long time to read all the
records (with access it doesnt take much time).
Also when I use the update-query it even takes much more time, more then 8
minutes, to save al the changes.
How can i speed up performance. It must be done more quickly, as it does in
access.

thx
 
Ad

Advertisements

W

William \(Bill\) Vaughn

Use Access?
In my experience, porting applications from DAO (Access) to ADO or ADO.NET
can be problematic as the conversion does not leverage the strengths of the
JET engine--it can't. When using a non DAO data access interface you have to
use the OLEDB JET provider which is a one-size-fits-all paradigm--not nearly
as efficient as the native DAO interface.

Of course, I also suspect that if you fetched fewer than all 25,000 rows
into memory at once you'll get better performance, but that's just a guess.
;)


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
C

cjard

John said:
Hi

I am working with an access database with more than 25000 records
(adresses).
In vb .net i use the tableadapter and it takes a long time to read all the
records (with access it doesnt take much time).
I dont know if youre aware of this, but Microsoft Access isnt a
database; it's a front end to the JET engine. As a front end, it sits
right on top of the database and is very closely interlinked with it.
When you open your 25000 long table in MS Access, it does show the
records very quickkly, but that's because it starts returning results
immediately - as soon as 50 or so rows have been returned they can be
displayed. Loading 50 rows is understandably quicker than loading 25000
If you observe more closely, open the table and start timing, then
click the navigate-to-end button and wait until the counter has settled
and the end of the recordset has appeared. This still may be quicker
than loading records through ADO, but that's more likely to be a
performance optimisation on access' part, working out how to show jsut
the end of the data set.

Taking this on board, we realise that access lives on top of a
pre-loaded 25000 strong record set and it doesnt need to load that data
anywhere else, only to show it. In your client app however, if youre
loading all 25000 and sending them all back then thats a huge
performance hit. It's also rather silly and I know you wouldnt write a
production system like that (100 users on a 1 million strong oracle
server would NOT each downlaod all million rows into the client)

Also when I use the update-query it even takes much more time, more then 8
minutes, to save al the changes.
There is a serious fault with the Update query; no query should take 8
minutes to update 25000 records unless it is really convoluted, or
requires a full table scan each time to find the necessary items
How can i speed up performance. It must be done more quickly, as it does in
access.
For reasons explained before, you have to appreciate that youre not
working with access here, youre developing a proper piece of software
that is disconnected from its data. DONT download all 25000 records
into the client, DONT send 25000 records back when you update just one.
Noone would expect a piece of software to download 25000 and then they
use the record navigators to find all the people whose name starts with
smith; you write your table adapter to take parameters and pull a
selection of results from the database. ensure you select the primary
key . ensure your tables HAVE primary keys. ensure your update query is
well formed and uses the primary key to update a single record

Google for DATA WALKTHROUGHS and have a read of microsoft's tutorials
 
Ad

Advertisements

G

Grant

If you really need to load/browse this many records then ADO.NET is
probably not a very good choice. Not only will it take a very long
time - but you will also chew up a lot of memory.

If you are building a desktop application that will only have a few
concurrent users then you would be much better off using a standard
ADODB connected recordset rather than an ADO.NET dataset.
Unfortunately ADODB recordsets cannot be databound to .NET controls.
Infralution however has a reasonably priced product (Virtual Data
Objects) that allows you use connected ADODB recordsets and data bind
them to .NET controls. You can get more information and download an
evaluation version from:

www.infralution.com/virtualdata.html

Grant Frisken
Infralution
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top