File Size and calculation time

[bearcub]

Yesterday I was working on a DB that was originally 6M and processing times were running normally. Then a couple of things happened and I'm not sure which action on my part my have corrupted the file.

I had imported a small table from another database and I converted a linked Excel table (the linked file contains 76 columns and over 67,000 rows of data) to a local table .

I noticed while I was working on creating reports and queries my computer started freezing up (like Excel would do if you have a lot of array formulas in a file or you have huge files open) for 10 to 15 seconds each time I tried to do something and my database increased to 77M (which I didn't know till I tried to send it to one of my colleagues)!

I don't know if the imported table created the log jam or converting the linked file to a local table or something else happened. I am using the StrConv function to convert some fields to Proper Case and I do have a macro that I was referred to yesterday from a question I submitted to the message board. I have a make table query running which I'm not going to include in the new DB because I think I don't need it.

I don't think any of these actions individually should cause the problem. I'm trying to figure out how to find out the cause so I don't keep on repeating the same mistake. Are they any tools in Access that might help me identify the problem?

I created a new DB yesterday and plan on rebuilding what I did. I do have that problem file open on my second screen while I redoing the new DB and I'm still have slow response to actions. I'm assuming that this is cause by my having the problem db open at the same time.

Any suggestions how I can figure out what is causing the slow processing time and large increase in file size?

Thank you for your help,

Michael

 

[xenou]

77 Mb is not really large. Maybe if it's all one table it's large-ish. What are you actually doing when it's "slow"?

Two general tips:

1. compact and repair regularly or at least occasionally.
2. index your most commonly-used search fields if you think it might help (for instance, if you very often search up people in your employee table by Last name, even though the real primary key on Employees is EmployeeID, then you might index Last Name).

 

[bearcub]

Right now, for example, I am just trying to create a report from a query. Every time I point and click at a control I have to wait 4 or 5 seconds before I see a response to my mouse click.

The thought occurred to me that maybe the functions I'm using in my query are volatile (like in Excel - indirect, offset, etc).

I'm using the StrConv function, a proper function (from VBA) and the left function on several of the query fields. However, I don't think a query recalculates like a spreadsheet would in Excel, would it.

I am also using a 32 bit computer for office 365 (maybe my computer has the wrong 365 version)- could that be an issue?

Michael

 

[xenou]

Nothing you are describing sounds like it should take any time at all. If you haven't rebooted your computer in a long while that's always a good place to start, btw, when your computer is acting very weird.

 

[bearcub]

I think I found the problem. In my one query I'm the StrConv function, Proper function and Left functions are being entered as calculated fields. I did fix those and it seemed to speed it up though it's acting funky again. I thought about rebooting the computer as well - sometimes it works sometimes it doesn't.

Thank you for the help.

 

[xenou]

Okay. Well, I'd reboot just to rule out all operating system variables. You might have to be more specific about this stuff you are doing with functions - basically, the rule of thumb is that simple queries run faster than complex ones, and large tables will take more time than small ones. Don't worry - I don't charge a lot for this kind of expert advice, at least not for the first consultation.

 

[james_lankford]

put all your tables in one db

check the datatypes

are your number fields really numbers or they text (255 characters)

are any fields memo (long text) ?
do they really have to be ?

or any fields text 255 when you know for sure they will only ever contain one character ?

fix any datatypes if you can

compact your database

that database should ONLY contain tables
NEVER anything else

now make a new database
LINK all the tables from the first database to this new database

create all queries, reports, macros, vba modules in this new database
if you have a make table query it should go in this new database

compact this db often

 

[bearcub]

That might be a good workaround. We do have a database like that already created. I'll look into it tomorrow and fix the data types as well.

Great idea about the other DB.

Michael

 

[bearcub]

Success! I created the table only DB and linked that table to another DB and created queries and reports in the child DB. Works great - no problems with performance.

Thank you for all your help and the cool suggestion about the 2 DB approach!

Michael