• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Should I be concerned about the gap in primary keys created when running an append query?

When I run an append query to update employees' skills based on attendance in a class in which certain skills were taught, multiple records match the initial query, but the records that need to be updated are relatively few. So, for 1,500 records matching, perhaps only 20 employees, the ones who attended the class, will be updated. The rest of the rows of records of the other unmatched rows are "deleted," for lack of a better understanding or description, so that there are multiple large gaps in the auto numbered primary keys of the training records. As I add training records, these gaps grow quite a bit wider, from the 100s to now, the 1,000s, and soon, the 10,000s.

I'm not sure if I'm describing very well, but it's the difference in the record amounts indicated by the dialog boxes while appending information.

Should those "missing records," or gaps, be a cause of concern as long as the data I want to be saved is being saved? Which, it is being saved. It just seems that the autonumbered primary key is going to become huge, quickly. Is that a performance hindering circumstance?

Perhaps there would be a better way to write my append query. One thought was to exclude records that didn't have a training date within the last X amount of days, but that would mess things up in case someone needed to update records for training prior to that time. I've attached a design and sql copy of my append query.
Append-Query-Design-View.PNG
Append-Query-SQL.txt
0
David Bigelow
Asked:
David Bigelow
  • 2
3 Solutions
 
SimonCommented:
Generally speaking, gaps in the auto-numbering don't matter at all or affect performance. The only time you'd worry is if it looks likely to exceed 2,147,483,647 (the max value for the long integer datatype).

You probably could improve the design on the process that you're using to udpate the training records, but I couldn't be sure from what I've seen. Ideally, you would only add rows for the people that need updating, perhaps by using an intermediate table to do the picking of the 20 out of the 1500 and then just pushing that 20 to the final destination table.
0
 
Jeffrey CoachmanMIS LiasonCommented:
It's typically is not worth the trouble to fix gaps in a "production" database.
Lets suppose that you have 5 customers with ID's of:
Bob=1
Sally=2
Greg=3
Betty=5
Tom=5
Now cust 3 (Greg) is deleted. (By accident or intentionally)
Now you have a "missing" value.
Now you need to create a system to "locate" all the missing numbers and fill them in first (before adding a new, automatically incremented number).
So using an AutoNumber filed becomes impossible (with this logic needed)
...and now you need to create an automated system to fill in the holes and deal with all of the related records (and add error handling, rollbacks, validation, ...etc)
:-O

Even if you did replace Cust3, with lets say "Adam", then what happens to all of the Orders that have 3 for greg's CustID??
...Now what if Greg comes back?
Does he get his old number back?
...it just gets too messy.

A Primary key will just guarantee uniqueness, ...not uninterrupted sequential values.
The user may not ever see the autonumber (you can create a CustomerID of an AutoNumber, ...but also have a CustRefID that is a concatenation of the CustomerID and some other value(s)
...so the "gaps" will only be noticeable by you.
At some point (after designing many tables in you DB),...it will not be worth the trouble of trying to order all of the tables over and over again,...
But worse, creating countless Update queries to shift related records around.

Now, for a "Test" database, things are different.
You can do all your testing and leave all the gaps,...
Then right before you "distribute" the app, ...you can:
Delete all the test data/records
Delete all the
Insert new autonumber fields (thus restarting all of them all to 0)
From that point on, ...you would have no real need to worry about "holes" in your autonumbers.

   
It just seems that the autonumbered primary key is going to become huge, quickly.

Not likely... at the autonumber theoretical limit of 2 billion(2,000,000,000) numbers, ...even if you created 250,000(quarter million) records *per day*, ...*every day*, for *20 years*, you would still only be at  1,775,000,000
;-)

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
corrected paragraph:

Now, ...for a "Test" database, things are different.
You can do all your testing and leave all the gaps,...
Then right before you "distribute" the app, ...you can:
1. Delete all the test data/records
2. Delete all the existing Autonumber fields
3. Insert new autonumber fields (thus restarting all of them all to 0)
From that point on, ...you would have no real need to worry about "holes" in your autonumbers.
0
 
PatHartmanCommented:
If the append query is creating gaps, it is because not all rows are being appended.  Some are being discarded probably due to key violations.  I would change the query to stop appending duplicates if possible.  To do that, you will have to join to the target table using a left join and only select rows where the id of the target table is null - that means that what you are appending is not already there.

Although the gaps in the autonumber won't affect anything, having them grow by 10,000 every time you run the append will make the autonumber grow huge in short order.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now