Solved

Long long wait for Loading Database Schema in DW mx 2004

Posted on 2004-08-11
4
130 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

25 Experts available now in Live!

Get 1:1 Help Now