slow database

[slechols]

I have a few Access 2003 databases that reside on another Windows XP PC
(which is acting as a server) that I access constantly. I haven't had any
speed problems until last week. I have one table in particular that I am
using as a test case to solve the problem. It has 20,000 records and 123
columns. I have never had any problems with it. Now, it is taking an
inordinate amount of time to load. When I click on it, the status bar says
"Running query...". I exported that same table to another database and it
accessed just fine.

I ran a query on the database that took 30 seconds to run. I went to the
server and ran the same query in 7 seconds.

 

[Duane Hookom]

Have you set the subdatasheet to None in the table design?

 

[slechols]

I set it to None (never had problems with subdatasheets before though). Now
when I open the table, the initial screen of data comes up immediately but
getting to the final record takes about 23 seconds (only 20,000 records).

 

[Jeff Boyce]

A table with 123 fields is probably ... a spreadsheet! A scan through this
(tablesdbdesign) newsgroup will reveal that a well-normalized table
structure will only rarely have as many as 30 fields. Your application may
benefit from further normalization.

Are the fields (columns) on which your query selects and/or joins indexed in
the underlying table(s)? If not, this can make a significant difference in
performance.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane Hookom]

I can't imagine why you would need to open a form/table to see 20,000
records.