Implementation Question

[robrich22]

I have a table for logging job details, and start/end times for the job. The data entry for the table is done via a continuous form. The table currently has over 100k records. I'd like to


automatically update the end time for a job, when the employee starts a new job. I'd like to avoid having to query over 100k records each time to check if the employee has any jobs logged
without an end time.

Just looking for ideas on the best implementation for this.. First solution I can think of is just query the table for USERNAME where JOBENDTIME = NULL, and then insert the current
time into the null field. But would this be efficient? Would the query search 100k records when trying to find the null endtime? The table is indexed on a unique job number field.

Thanks

[pbaldy]

That's what I would do, an update query with those two criteria. You may want to add an index on the user field.

[robrich22]

Thanks for replying. That is the solution I went with. I was just worried that an UPDATE command on a table with over 100k records would be inefficient. But it didn't seem to flinch. I might temporarily store the open jobs in a different table, and then commit them to the main jobs table after the end time/date has been entered for the job.

[pbaldy]

I personally wouldn't bother with the temporary table, unless you ran into performance issues. I doubt you will. With the 2 criteria, the database engine should handle it with ease.