Why do DB-s sometimes have "extra" tables

[techexpressinc]

I work at a place with a couple dozen MS-Access DBs. Many are Very Slow to work with. And a some have "extra" tables when I view the table relationships. What are those all about?

Here is a snapshot of the relationships.

http://www.box.net/shared/szmsd18omu

Thanks for insights

Russ @scaninc.org

 

[techexpressinc]

These "extra" tables I guess are created by all those relationships people have created for "drop down" lists etc.

I need better system performance, it is sooo bad now a query to pull 1700 records from a table of 16000 records takes 5 minutes then to jump to the bottom of the output of the query another 10 minutes, just to see i did not get what I need. This is effecting everyone's productivity that works with the DB.

To get better performance to get results would a good idea be to copy the DB and break as many of the relationships as I can. Then do my queries??

Thanks for help Russ rneuman @ scaninc.org

 

[MSAccessRookie]

These "extra" tables I guess are created by all those relationships people have created for "drop down" lists etc.

I need better system performance, it is sooo bad now a query to pull 1700 records from a table of 16000 records takes 5 minutes then to jump to the bottom of the output of the query another 10 minutes, just to see i did not get what I need. This is effecting everyone's productivity that works with the DB.

To get better performance to get results would a good idea be to copy the DB and break as many of the relationships as I can. Then do my queries??

Thanks for help Russ rneuman @ scaninc.org

Without seeing any of the queries, that is very difficult to tell, but:

• The User Information List appears THREE times. This could cause a lot of slowness all by itself if any (or more likely all) of these relationships are within the same query.
• The database could be more Normalized, as there are several instances of duplicated data (Created, [Created By], and [File Type] are all in both the Table User Information List as well as the Table [Caregiver Tracker] to name 3)
• The Relationship list does not look like it is displaying all tables

If you can show us some of your Queries, then perhaps we will be able to assist you further.

 

[Banana]

The pic was kind of smallish and took a while to trace the lines; this is just my best guess. It looks like they want to make sure only ID of User Information List goes into "Created By" and "Modified By", which is fine. I do not think it is the culprit here, but I concur with Rookie that there's more to the picture.

Is your database split? If it is, and your front-end doesn't have any tables at all, there's your problem. Your lookup tables should be distributed in front-end because if it's on backend, it has to be retrieved over network and that's massively slow compared to getting it from local hard drive.

Likewise, basing the form on tables themselves will cause same problem. They should be based on a query with a WHERE criteria instead to send only the needed rows.

HTH.

 

[HiTechCoach]

I would also like to recommend this:


Subdatasheet Name property set to [none]

Track name AutoCorrect should be off.


See:
Microsoft Access Performance FAQ
http://www.granite.ab.ca/access/performancefaq.htm

 

[techexpressinc]

The database is not split. I seen this on other MS-Databases here. It seems the "magically" appear from the creation of drop-downs on the datasheet for a table.
This one database is Very Slow on doing queries. Although, there are only 16 thousand records. I have dumped the two primary tables to Excel format that I was doing the query against. Then and re-loaded them into a temp DB with just the 2 tables, did the same query the time went from never-never land 20 minutes+ to 30 seconds.

 

[boblarson]

The database is not split. I seen this on other MS-Databases here. It seems the "magically" appear from the creation of drop-downs on the datasheet for a table.
This one database is Very Slow on doing queries. Although, there are only 16 thousand records. I have dumped the two primary tables to Excel format that I was doing the query against. Then and re-loaded them into a temp DB with just the 2 tables, did the same query the time went from never-never land 20 minutes+ to 30 seconds.

I couldn't make out the field names because the screenshot was way too small and when enlarged it became unreadable. Based on the limited view I got to see, I think you have more going on than appears on the surface. I think we would be able to give you much more information if we had the database available to look at. You should compact it using Tools > Database Tools > Compact and Repair first though.

 

[The_Doc_Man]

There is no substitute for a regular schedule of database maintenance to do things like the Compact & Repair options.

I tried but could not open the link to the www.box site. My site firewall blocked me. Therefore I cannot see the tables you say are being created. How many of these "new" tables are there and what sort of information do they contain? If you are the administrator of the database, can you look at the new tables with the security tools to see who owns them? What I'm interested in knowing is who creates the tables?

Normally, unless you have a bit of VBA code or a macro running a MAKE_TABLE query, you won't silently create a table that you didn't know you created. So if you aren't doing it, who is?

 

[boblarson]

From what I can see (the pics are still ridiculously small and when enlarged you can't read them), these "tables" are just the same table which it shows as two tables because it needs to show it thusly to show the lookup relationship between two different fields into the same table.

 

[dkinley]

I am willing to bet that if you expand the windows of the User Information List, you will see these are different representations of the same table ...

User Information List, User Information List 1, and User Information List 2.

Perhaps someone can enlighten me on this, but I think that these 'copies' are for reference purposes only and would not pose a problem in general. Following, I think that whoever designed the relationships added the table more than once to help them visually see how each table was joined to others for look-up purposes. I would think if this was a fact, they would have moved the tables around so give a better visual layout to plainly see how the links are created (instead of running behind other tables to get across the screen).

What I would say is the problem is the left-hand User Info List is joined the upper right-hand Info List (and the bottom right hand Info List). This is causing recursion on the tables (to the third degree?). I can understand the thought because if a new user is created in the list, you are asking what user from the same list created/modified the new/existing user. I recall that this can create hell in queries, to what extent - I have no direct knowledge of, just some stuff I have read in the previous past. It might not be the design of that is causing but the way it is implemented and queried. Again, a definite point of enlightment would be required because I do not have knowledge of the problems from recursion in this instance (i.e., if creating indexes on the foreign keys would alleviate, etc).

-dK

 

[boblarson]

In other words, I think that whoever wrote this added the table more than once to help them visually see how each table was joined to others.

Actually, for setting up relationships where you use the same field from the same table joined to two different fields, this is exactly the way you do it (add two instances of the same table) otherwise it doesn't want to set things up quite right if you tried to link the same field from one table to two fields of the other table.

 

[dkinley]

Actually, for setting up relationships where you use the same field from the same table joined to two different fields, this is exactly the way you do it (add two instances of the same table) otherwise it doesn't want to set things up quite right if you tried to link the same field from one table to two fields of the other table.

I have used it in the past, never saw a detrimental effect so ran with it - never bothered researching ...

I had the word 'instantiation' in my post before editing - was afraid it might taken literally and confusing my first point. Glad to see it is okay to use in this context, though, because that is how I think of it in my head.

-dK