Solved

Long long wait for Loading Database Schema in DW mx 2004

Posted on 2004-08-11
4
128 Views
Last Modified: 2010-04-25
On one of my Access tables, which has about 40 columns, I have to wait several minutes! On smaller tables much less. I am on broadband and never had this problem before. The problem is worst  when using INSERT RECORD dialog. Sometimes, after waiting all that time, the column field is still not populated and I have to start all over.

I read old messages here and one person who had this problem was using 10 recordsets on a single page. I am not doing that. Just one recordset.

Does anyone know why this happening?

Could this mean that my Access 2000 database is corrupted? Is there a tweak ?

thanks.

Oh, forgot to say: the database is on remote.
0
Comment
Question by:polaatx
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:alexhogan
ID: 11773864
40 columns in a single table isn't really something that you want to see happen in your database.

However, here is a couple of things that you should look into.

If you haven't already, create a primary key on a unique column.
Create an indexes on key fields.
Normalize your database.
Optimize data type in your database.  Don't store varchar values where int will work.

Remember that Access has a 1 gig limit.  As the size of your database grows, the greater the importance of normalization, indexing and strict datatyping.  Especially as it starts to reach it's limit.
0
 

Author Comment

by:polaatx
ID: 11778731
Hi alexhogan,

Could you please explain what you mean by "Create an indexes on key fields." ?

This is an Access 2000 database, so I don't think the varchar example applies to me. I am using memo only when I really need it and otherwise it is text.
0
 
LVL 8

Accepted Solution

by:
alexhogan earned 500 total points
ID: 11778847
That's a little bit of a typo.., :-}

It should read, "Create indexes on key fields".

Creating indexes allows databases to query and sort records faster.
To set an indexed field in Access;
1. Select a field that is commonly searched
2. Change the Indexed property to Yes (Duplicates OK) if multiple entries of the same data value are allowed or Yes (No Duplicates) to prevent duplicates.

This will speed up the query.

You will still take a hit because of the table size. (I'm talking about the number of columns)
0
 

Author Comment

by:polaatx
ID: 11805804
thanks for the explanation of the indexing. I checked all the userID fields in every table are already set to be indexed. I guess Access did it by default.

I didn't find a index setting the queries. Can you set the index for those too?

Additionally, is there a way in ASP VB to put the contents of a recordset in some sort of already-compiled format that the application can pull immediately? I am thinking perhaps something like a stored procedure that already has the most requested data ready to go.  I am calling on a couple of tables that contain search categories from sidebar of every page on the site.  (If you want, I can post this question as a new question).

I am very concerned about what you said about 1 gig limit on Access. My database is 932kb (and it doesn't contain any data!). It will be much more, especially because it has memo fields that will contain text for entire pages.

Here's the database. If you can look at it perhaps you can tell me why it is so big already: aasg.brinkster.net/database2/aura.mdb  
(If you want, I can post this question as a new question).
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now