Execute plan of MS- Access for composite indexing

Question

Hello friends

I have a Query that consist of INNER JOIN and I want to use Composite index.I dont know exactly which fields should be indexed. I read that composit index depand on execute Plan of DBMS .Is it correct? how can I find execute Plan for MS-accesss?

this is my Query:

SELECT DISTINCT tb_Bauteile.ID,
                tb_Bauteile.Name,
                tb_Bauteile.Blatt_nr,
                FehlerCodes_akt_Liste.Fehlerpfad,
                FehlerCodes_akt_Liste.Pfad_Bezeichnung,
                FehlerCodes_akt_Liste.Steuergerät,
                FehlerCodes_akt_Liste.Kommentar
  FROM ((tb_Pinnummern INNER JOIN tb_Bauteile ON
        tb_Pinnummern.Bauteil = tb_Bauteile.ID) INNER JOIN tb_Fahrzeug ON
        tb_Pinnummern.SG = tb_Fahrzeug.Motor_SG)
 INNER JOIN FehlerCodes_akt_Liste
    ON tb_Bauteile.CDT = FehlerCodes_akt_Liste.CDT
 WHERE (((tb_Bauteile.Blatt_nr) LIKE "5*") AND
       ((tb_Fahrzeug.ID) = forms ! frm_fahrzeug ! id))
 ORDER BY FehlerCodes_akt_Liste.Fehlerpfad;

Answer 1

A very simplistic way is to use SHOWPLAN. It is limited in that it cannot analyze sub queries. To turn SHOWPLAN on you need to set a reg key. If you have Access open close it before running the .reg file.

Paste this into a text file and save it with an .REG extension (NOTE: This works for Access 2003 running on Windows XP. You will have to edit the key location to fit your OS and Access version) :

Windows Registry Editor Version 5.00 
;REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\JET\4.0\Engines\Debug]
"JETSHOWPLAN"="ON"
;End

Be sure to turn it off when you don't need it as it does slow execution down. Run this as a .REG file to turn it off:

REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\JET\4.0\Engines\Debug]
"JETSHOWPLAN"="OFF"
;End

The result file ends up in your default database folder.

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals