Solved

Long long wait for Loading Database Schema in DW mx 2004

Posted on 2004-08-11
4
134 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

726 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