performance and readability

  • Thread starter Thread starter Christopher Glaeser
  • Start date Start date
C

Christopher Glaeser

Consider the following:

tblWorkOrders
WorkOrderID Primary Key
WorkOrderTypeID Foreign Key
[many other fields]

tblWorkOrderTypes
WorkOrderTypeID Primary Key
WorkOrderType Text
1 Inspection
2 Repair
3 Replacement

Question: When designing a query to find all WorkOrders that are Repairs, is
it good practice and will it improve performance to compare WorkOrderTypeID
to the number 2 (which assumes the ordering of WorkOrderTypes is fixed), or
is it better to do the string compare looking for the string "Repair"? I'm
guessing the latter is easier to read and less error prone, but has
performance implications.

Best,
Christopher
 
If you are searching tblWorkOrders, you must search for 2 since "Repair" is
not stored in the table. There shouldn't be any issues with easier to read
or less prone to errors by using the numeric value in the query. Criteria
for queries should come from controls on forms. Users should never see the
values 1, 2, and 3.
 
If you are searching tblWorkOrders, you must search for 2 since "Repair"
is not stored in the table.
Click to expand...

Perhaps what I did is bad design, but in the query designer, I added both
tables tblWorkOrders and tblWorkOrderTypes. I then added the text field
WorkOrderType to a column and put the string "Repair" in the Criteria with
Show deselected. This returns the correct data (WorkOders that are
Repairs), but I'm concerned there are serious performance implications.

Best,
Christopher
 
I doubt you will have much performance issues unless you have 100,000+
records. You would get the best performance if you filter against
tblWorkOrders and not against tblWorkOrderTypes.
 
Back
Top