Hello team,
I have an Access database, I have imported 7 or 8 tables.
I came to update the first table; I added a field and then I wanted to add content to that field all across 100,000 rows; it took over more than 2 hours and eventually my system froze. When I looked back into the table, fields up to some rows were populated; even among those populated, some rows didn't get the text.
What should I do to speed up the update query?
I am now running compact and repair; that also it is taking a long time!
What is the suggestion? How boost up the performance of these queries?
Hello Hans,
The tables don't have any specific design. They are just business tables which MemberID can be used as a PK, but the fact that PK is also duplicated.
Any help is greatly appreciated.
Regards,
BittenApple
If the PK can be duplicated, THEN IT ISN'T A PK! Is there a real PK for the table? If you can't create one with existing fields, then add an autonumber field as the PK. Access works best with tables that have a PK.
Also, does your update query join tables? If so, are the connecting fields indexed? Does your query use functions like DLookup? If so, that would kill performance. As Hans said, "Without knowing the design of the table and the query that's impossible to tell".
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
Hello,
Mark, I don't need a primary key, because I have memberID that are unique but are being repeated several time across the table. The reason for it is that each record is unique, a memberID might have more than one diagnosis code. but we might have more than one memberID in a table. If I join the tables through several fields, I will get the unique records. I do understand that PK is different than joining tables through multiple fields. However, for my purpose, I don't want to create a pk with combining several fields.
Hans, This is design of my table: I have 36 fields and every field has a text datatype, except those fields which contain date and I have an age field that has integer. I hope this helps you to understand the design of my table. I read a couple of points on how to boost the performance of Microsoft Access, and I am trying to load less data, but I need something more effective than these.
The statement "I have memberID that are unique but are being repeated several time across the table" is a contradiction: if the memberIDs are repeated, they are NOT unique!
Please answer Mark's questions instead of giving nonspecific information.
Hans and Mark,
I am so sorry for not being so specific.
these are the answers:
Also, does your update query join tables?
No, the update query is not joining tables, my update query is only related to a field of an individual table.
If so, are the connecting fields indexed? I have not indexed any field, since the indexing in some areas slows down the query. As I mentioned on the top, the update query is only on one field and on one table.
Does your query use functions like DLookup? No, there is no function involved in my query.
As Hans said, "Without knowing the design of the table and the query that's impossible to tell".
This is the design of my table:
I have 36 fields and every field has a text datatype, except those fields which contain date and they have date data type, and I have an age field that has integer data type. All the fields are repeated, although each record is a unique record. I hope this helps.
Regards,
BittenApple
Hello Hans and Mark,
Hans: "See if creating an index on the field that you update makes a difference.
The field which is getting updated doesn't have any value, should I still create an index on it?
Mark:" I don't need a primary key, because I have memberID that are unique but are being repeated several time across the table.<<
This indicates to me that you don't understand the concept of a Primary Key.
Again, I'd suggest you create a field named perhaps "RecordID", make it an autonumber, and designate it as the Primary Key."
Hello Mark, Although I have read about PK a lot, you are probably right. Since down the road, I have to join the tables, just autoNumber in one table doesn't relate to an autoNumber in different table unless I create complex PK; I thought that if I want to relate the table through multiple fields, why should I composite primary key which adds to the complexity of my database? Please advise me on this.
I will create an autoNumber and then after update the table, I will delete it.
Please advise me.
Regards,
BittenApple
p.s. I watched a couple of video tutorials on composite primary key. In this case, If I want to create a composite pk, I have to move the fields for pk adjacent to each other and Access might not let me do it.
Hello Hans and Mark,
Thank you for your help,
Hans: "See if creating an index on the field that you update makes a difference."
It is unbelievable, I put an index on the field and update was done so quick and fast. I learned it hard, but I learned it.
Regards,
BittenApple
