Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004

    Unanswered: Query Performance Question

    Let's say I have a query with two tables joined. The tables do not have any columns with the same name. My question is does Access benefit from putting the table name in front of the column name? For example by using

    SELECT tbl1.Key FROM tbl1 INNER JOIN tbl2 ON tbl.Key = tbl2.ID

    Instead of

    SELECT Key FROM tbl1 INNER JOIN tbl2 ON Key = ID

    I ask because Access always uses [tableName].[Column] even if the column names are unique. I am creating the queries in VBA and usually use just the column name to shorten the code.

    My thought was that with the table name prefix Access knows right away what column I am referencing, but without the prefix Access has to determine which table and then find the column. If this is the case and I can gain some performance be using the table name then I will.


  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    I don't believe that any gains will be made by including the table name in the reference. The reason Microsoft does it is so it can guarantee that the field you added will be taken from the proper table. If two tables have the same field name (which can happen frequently) then trying to figure out which table you want is impossible without user intervention.

    Creating indexes and setting up Relationships will help Access optimize your queries.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts