Form based on query opens slow

[kjjames via AccessMonster.com]

I will try to provide as much info as possible without making my question too
complicated...

I have a search form based on Allen Browne's search form, which works great.
The form's source is a query (provided below).

The basic format of the database is that it holds resource info, books,
magazines, etc. Each publication has many projects. Some projects have
multiple techniques or topics or types associated with them. So, in order to
be able to associate the many-to-many link, there are tables as follows:

tblProjects:
ProjectID
ProjectName
ProjectNotes, etc

tblProjectType
ProjectTypeID
ProjectType

tblProjectTypeProject (many-to-many table)
ProjectTypeID
ProjectID

Again, the form performs the search perfectly. The issue is that when I open
the form, it takes a while to open. I have it set so that no records will
show when the form opens. At this point, I have around 1300 projects entered.
If I need to restructure my tables, I will do that. I still need to be able
to enter multiple Project Types, Topics and Techniques, so suggestions on how
to do that would be great. Or, do I need to restructure my query. Any help
would be appreciated.

Thank you. Karen

SELECT tblProjectType.ProjectType, tblTechnique.Technique, tblTopic.TopicName,
tblPublications.PublicationTitle, tblPublications.Volume, tblPublications.
Issue, tblPublications.Date, tblProjects.ProjectName, tblProjects.
ProjectNotes
FROM (tblTopic INNER JOIN tblTechnique ON tblTopic.TopicID = tblTechnique.
TopicID) INNER JOIN (tblPublications INNER JOIN (tblProjectType INNER JOIN
(tblProjects INNER JOIN ((tblProjTypeProj INNER JOIN tblTechProj ON
tblProjTypeProj.ProjectID = tblTechProj.ProjectID) INNER JOIN tblProjectTopic
ON tblTechProj.ProjectID = tblProjectTopic.ProjectID) ON (tblProjects.
ProjectID = tblTechProj.ProjectID) AND (tblProjects.ProjectID =
tblProjTypeProj.ProjectID) AND (tblProjects.ProjectID = tblProjectTopic.
ProjectID)) ON tblProjectType.ProjectTypeID = tblProjTypeProj.ProjectTypeID)
ON tblPublications.PublicationID = tblProjects.PublicationID) ON (tblTopic.
TopicID = tblProjectTopic.TopicID) AND (tblTechnique.TechniqueID =
tblTechProj.TechniqueID);

 

[Jerry Whittle]

Hi,

The SQL statement looks straight forward. About the only possible problem is
the the tblPublications.Date field as Date is a reserved word. Even so I
doubt that's causing this particular slowdown. Find out more about using
reserved words at:
http://support.microsoft.com/kb/286335/

Next we need to know the Primary Key fields for the tables involved. Ideally
at least one side of each join is with the Primary Key field. Also the tables
should be joined in the Relationships window with Referential Integrity
enabled.

One advantage of PKs in the join is that a PK is indexed. However in your
case each of the join fields has "ID" in them. By default Access also indexes
any field with ID, key, code, or num as part of the field name. Therefore all
the joins are indexed. In fact the problem might just be that there are two
indexed on the same field: one for being the PK and the other for having ID
in the name. If you see duplicate indexes on a field, try dropping the one
that isn't part for the PK.

One big question is how does the query run by itself? How long does it take
to start displaying records, and maybe more importantly, how long before you
can scroll down to the last record?

Does the form, or any of the fields, have any code on one of the starting
events such as On Current, On Load, or On Open? They could be slowing things
down.

Also fancy artwork on the form could be a problem.

 

[Jerry Whittle]

One other thought: Disable your virus checker temporarily for testing. You
might just be surprised.

 

[kjjames via AccessMonster.com]

Hi,

The SQL statement looks straight forward. About the only possible problem is
the the tblPublications.Date field as Date is a reserved word. Even so I
doubt that's causing this particular slowdown. Find out more about using
reserved words at:
http://support.microsoft.com/kb/286335/

Next we need to know the Primary Key fields for the tables involved. Ideally
at least one side of each join is with the Primary Key field. Also the tables
should be joined in the Relationships window with Referential Integrity
enabled.

One advantage of PKs in the join is that a PK is indexed. However in your
case each of the join fields has "ID" in them. By default Access also indexes
any field with ID, key, code, or num as part of the field name. Therefore all
the joins are indexed. In fact the problem might just be that there are two
indexed on the same field: one for being the PK and the other for having ID
in the name. If you see duplicate indexes on a field, try dropping the one
that isn't part for the PK.

One big question is how does the query run by itself? How long does it take
to start displaying records, and maybe more importantly, how long before you
can scroll down to the last record?

Does the form, or any of the fields, have any code on one of the starting
events such as On Current, On Load, or On Open? They could be slowing things
down.

Also fancy artwork on the form could be a problem.

I will try to provide as much info as possible without making my question too
complicated...

[quoted text clipped - 45 lines]

TopicID = tblProjectTopic.TopicID) AND (tblTechnique.TechniqueID =
tblTechProj.TechniqueID);

Jerry,

Thanks so much for the reply.

Okay...I went over everything in your post.

I fixed Date (she writes with head hung low).

I ran the query on on it's own and it was quick. I was able to move to the
last record quickly too.

I checked my coding. I did have coding on the form's OnOpen Event. It was
set up to ensure that the form was clear when I opened it. I disabled that
and now the form opens up very quickly.

I disabled my AV software. No difference there.

As for the indexes. You are right that all of my Primary Keys are indexed.
I am just not sure where I should be dropping the index. From the original
table? The joining table? Oh, referential integrity is enabled as well.

Once again, Thank You! Karen

 

[Jerry Whittle]

Check all the tables where there's an ID in the field name. See if there are
two or more indexes on that field. If so, delete the index that does not have
the little gold Primary Key next to it.

But is seems that it was just the code slowing things down.

Good work on having RI enabled.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

The SQL statement looks straight forward. About the only possible problem is
the the tblPublications.Date field as Date is a reserved word. Even so I
doubt that's causing this particular slowdown. Find out more about using
reserved words at:
http://support.microsoft.com/kb/286335/

Next we need to know the Primary Key fields for the tables involved. Ideally
at least one side of each join is with the Primary Key field. Also the tables
should be joined in the Relationships window with Referential Integrity
enabled.

One advantage of PKs in the join is that a PK is indexed. However in your
case each of the join fields has "ID" in them. By default Access also indexes
any field with ID, key, code, or num as part of the field name. Therefore all
the joins are indexed. In fact the problem might just be that there are two
indexed on the same field: one for being the PK and the other for having ID
in the name. If you see duplicate indexes on a field, try dropping the one
that isn't part for the PK.

One big question is how does the query run by itself? How long does it take
to start displaying records, and maybe more importantly, how long before you
can scroll down to the last record?

Does the form, or any of the fields, have any code on one of the starting
events such as On Current, On Load, or On Open? They could be slowing things
down.

Also fancy artwork on the form could be a problem.

I will try to provide as much info as possible without making my question too
complicated...

[quoted text clipped - 45 lines]

TopicID = tblProjectTopic.TopicID) AND (tblTechnique.TechniqueID =
tblTechProj.TechniqueID);

Jerry,

Thanks so much for the reply.

Okay...I went over everything in your post.

I fixed Date (she writes with head hung low).

I ran the query on on it's own and it was quick. I was able to move to the
last record quickly too.

I checked my coding. I did have coding on the form's OnOpen Event. It was
set up to ensure that the form was clear when I opened it. I disabled that
and now the form opens up very quickly.

I disabled my AV software. No difference there.

As for the indexes. You are right that all of my Primary Keys are indexed.
I am just not sure where I should be dropping the index. From the original
table? The joining table? Oh, referential integrity is enabled as well.

Once again, Thank You! Karen