Inserting Index in tables to expedite runtime in Access
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Inserting Index in tables to expedite runtime in Access
Hello team,
I am inner joining two tables and it takes a long time. I wonder If I can insert an index on Primary Key to shorten runtime?
Please advise me.
BittenApple
I am inner joining two tables and it takes a long time. I wonder If I can insert an index on Primary Key to shorten runtime?
Please advise me.
BittenApple
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Inserting Index in tables to expedite runtime in Access
There is always an index on the primary key of a table, because the primary key is an index itself.
Is there an index on the foreign key in the other table? If not, it might help to create one.
Is there an index on the foreign key in the other table? If not, it might help to create one.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Inserting Index in tables to expedite runtime in Access
Hello Hans,
Is converting to SQL sql server would be another option if we don't use index option?
regards,
BittenApple
Is converting to SQL sql server would be another option if we don't use index option?
regards,
BittenApple
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Inserting Index in tables to expedite runtime in Access
SQL Server tables need indexes just like Access tables, so I don't see the difference.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Inserting Index in tables to expedite runtime in Access
Hans,
I am looking through my old posts, looking for something and reading my old post to refresh my memory and remember things.
I saw this post here. I do really like indexing the join fields.
So many thanks,
BittenApple
I am looking through my old posts, looking for something and reading my old post to refresh my memory and remember things.
I saw this post here. I do really like indexing the join fields.
So many thanks,
BittenApple
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 331
- Joined: 11 Feb 2010, 03:55
- Location: Land O Lakes, FL
Re: Inserting Index in tables to expedite runtime in Access
Just out of curiosity, how big are the 2 tables you are trying to join in that query? That is, how many records in each table?
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Inserting Index in tables to expedite runtime in Access
Hello Mark,
Thanks for you attention, the tables have 140,000 and 50,000 records.
Regards,
BittenApple
Thanks for you attention, the tables have 140,000 and 50,000 records.
Regards,
BittenApple
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Inserting Index in tables to expedite runtime in Access
With those sizes, it's essential that there is a primary key on the 'one' side of the join, and an ordinary index on the 'many' side.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Inserting Index in tables to expedite runtime in Access
Hello Hans & Mark,
how?
I set the MemberID as PK on Left table (I did a select and Make-table query, is there any other way other than this?); on right hand side table (I did a select and a make-table query as well); I inserted a new field on the right hand side table and called it Visit ID and the type of field was autoNumber; I had to save the table, I got an error message.
What I did was that I copied the structure of make-up table and pasted it, then I added AutoNumber and then updated the table with the content of right hand side table.
Now, I have faced with this question: how the left hand side table which has MemberId as a Pk to be linked to right hand side table with has visitID (AutoNumber as a PK)? How this can be handled? I am in dismal; although I have normalized tables and inserted pk on each table.
Regards,
BittenApple
how?
I set the MemberID as PK on Left table (I did a select and Make-table query, is there any other way other than this?); on right hand side table (I did a select and a make-table query as well); I inserted a new field on the right hand side table and called it Visit ID and the type of field was autoNumber; I had to save the table, I got an error message.
What I did was that I copied the structure of make-up table and pasted it, then I added AutoNumber and then updated the table with the content of right hand side table.
Now, I have faced with this question: how the left hand side table which has MemberId as a Pk to be linked to right hand side table with has visitID (AutoNumber as a PK)? How this can be handled? I am in dismal; although I have normalized tables and inserted pk on each table.
Regards,
BittenApple
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Inserting Index in tables to expedite runtime in Access
Does the right-hand table have a field MemberID that corresponds to MemberID in the left-hand table?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Inserting Index in tables to expedite runtime in Access
Yes, it does.
Regards,
BittenApple
Regards,
BittenApple
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Inserting Index in tables to expedite runtime in Access
So MemberID is the primary key in the left-hand table, and an ordinary field (not the primary key) in the right-hand table. You should link the tables on MemberID.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Inserting Index in tables to expedite runtime in Access
Hans,
Ok, I will.
Why would you say ordinary key instead of FK?
Is there a method to populate the left hand side table with the autoNumber (VisitID) of right hand side table?
Why did we generate PK in this context for right hand side table? We can normalize the left hand side table only and then assign MemberID as a Pk and then link it to right hand side table on MemberID. What is the benefit of generating PK for right hand side table? For referential integrity? We can join through MemberID and enforce referential integrity.
I hope these all make sense.
BittenApple
Ok, I will.
Why would you say ordinary key instead of FK?
Is there a method to populate the left hand side table with the autoNumber (VisitID) of right hand side table?
Why did we generate PK in this context for right hand side table? We can normalize the left hand side table only and then assign MemberID as a Pk and then link it to right hand side table on MemberID. What is the benefit of generating PK for right hand side table? For referential integrity? We can join through MemberID and enforce referential integrity.
I hope these all make sense.
BittenApple
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Inserting Index in tables to expedite runtime in Access
By ordinary field I meant that the MemberID field in the right-hand table is not the primary key in that table.
As far as I can tell, the left-hand table should not contain a VisitID field.
With a very small number of exceptions, every table should have a primary key. It is always handy to have a field that uniquely identifies the records in a table.
As far as I can tell, the left-hand table should not contain a VisitID field.
With a very small number of exceptions, every table should have a primary key. It is always handy to have a field that uniquely identifies the records in a table.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 331
- Joined: 11 Feb 2010, 03:55
- Location: Land O Lakes, FL
Re: Inserting Index in tables to expedite runtime in Access
Building on Hans' last post, you also need to understand that if you create a relationship between 2 tables and if you Enforce Referential Integrity, then Access creates a hidden index for you on that foreign key in the child table. In Hans' example (if EFI was specified), Access would have created an index on the MemberID field in the Visit table; so there is no need to manually index this field.
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.