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
