Speed? Linked Tables?

[John64]

I have a newly created access 2007 database and have just started to import old historical data over from excel sheets to my backend. There is one table in particular that has grown very large. It is over 1 million lines and will be well over 2 million by the time it is finished. It is 6 columns wide. The file itself has grown to 61 mb. Any queries, charting, or viewing of the data of any kind has become extremely slow, even if i place the files on my own computer instead of the network. The data is in such a way that I can't see how to create a primary key and the field wich is typically searched through is already indexed in the design view of the table. This indexed field has many duplicates and that is why it isn't a primary key. One site I read suggested keeping the linked backend open as long as the frontend is opened through code, but I am unsure if this would help or how exactly to do it. It was also suggested on the site that I turn off the compact and repair on close, but this seems weird cause I always figured that helped speed.
Any suggestions on what could be done or what to look into would be helpful. Thanks

 

[gemma-the-husky]

if you have a million rows, and you cannot select based on a key, then ANY system will have to read every record and determine whether to include it or not - and it will not be fast.

if you really cant get a key (and i understand this - i have a similar system where an option group setting changes the selected rows, but in most cases the selection of a row depends on a function that has to be evaluated FOR each row)

then the only solution is toi try and archive as much stuff as possible out of the table into an archive table on a regular basis - this obviously reduces the number of rows to be read by your queries etc

if not then the only other thing i can suggest is try and get some overnight process that could compress your data into a new table, ready for use the next day - it's not real time, but it might be quicker and worth the trade-off

 

[Simon_MT]

Tips on performance even with so many records:

Try:

Creating a table even with one record and create a Menu From based on that record.
The idea here is that the ldb (locking file) remains open along as a user is active in the database, this reduces the overhead to the first user not every user.

If you are using 2003 + change the SubDataSheetName property in a Table to [None]

Simon

 

[John64]

I had some more time to mess around with this and I found that the lag in directly viewing the table is caused by the linking. Simply copy pasting the tables into the .mdb frontend eliminates the extreme wait when viewing the table directly, however, the queries and forms are still slow, although they may have sped up some. I set the subdatasheet name propertey to none and I am not sure if that helped any. I am unsure how to accomplish the menu which will keep open the idb file, but I observed the folder as I was going through tables, queries, and my menu system and it appears to be staying open. It never ceased to exist in the windows folder view. I have a menu that never closes, although it doesnt access any of the tables it may be keeping the idb open. I am unclear as to how archiving the data works. I don't know anything about that. Another possible problem I stumbled into on the internet is that I used the reserved word "date" for one of my field names in this table.

Is my reserved word causing this?
Is there a page or something that explains the process of archiving?
Why is the linking causing this lag when viewing the table directly?
Would simply creating an autonumber in front of all the data help any?

 

[Simon_MT]

In your Menu System bind the first Menu to a table or query at the backend.

You could also try the Database Analyser and see if that comes up with anything useful.

Simon

 

[John64]

Since my menu was created with the switboard, I tried moving the switchboard to the backened and linking the menu through record source. I also tired simply creating a list box and tying it to a table in the backened. Neither way seemed to work. Could you please explain how I bind to the backend. Thanks

 

[Simon_MT]

I have never used a switchboard so forgive my ignorance here's what I do:

http://www.designedlogic.com/Menu.html

This Menu is bound to a backend table.

Simon

 

[The_Doc_Man]

If you have a startup form or always-open switchboard form, never exit from it. (Minimizing it is OK.) Have it set up a timer to requery a short recordset from your backend. Keep the recordset open between queries. That's how you keep a backend open. Or just explicitly open it by name.

 

[shadow9449]

If you have a startup form or always-open switchboard form, never exit from it. (Minimizing it is OK.) Have it set up a timer to requery a short recordset from your backend. Keep the recordset open between queries. That's how you keep a backend open. Or just explicitly open it by name.

What I usually do is bind the logon screen to a table with just one record in it and then hide the logon screen. The table that it's bound to is in the back end and linked to the front end. I've never had the logon screen requery the recordsource. Would I get better results if I did the latter? I've never heard of this approach. How often would you have it requery - every second, every minute?

Thanks

SHADOW

 

[John64]

Well I didn't find a supper great fix like I was thinking, but I believe I borrowed off the "archiving" idea and found something that would work. I decided that many of the functions in my menu have no need to be looking at all 10 plus years of data that is in the table, so I simply created a button which will create a table of only the last 40 days of data. I then tweeked all the queries that really didn't need to be going back so far. This way at least not all of my queries and forms are slow.

Thanks to all.

 

[gemma-the-husky]

a couple of other thoughts

with a LAN, the data to be processed has to be brought back to your PC - so for a big file, this could add a substantial overhead.

if you can come up with a query to limit the number of columns or rows, that have to be fetched this can help. But this is easier to do in SQL (although I am no expert) which is why Access can start underperforming with big datasets.

One other possibility is windows issues that can affect performance with linked tables

look at this MS Knowledge base article - particularly the bit about sharing violation

http://support.microsoft.com/kb/889588