Way to tell which Index(s) a query is using??? 2

[kkitt]

Is there anyway to tell which index are being used in a query?

I want to be able to verify that these are using the best index available or if the query needs to be adjusted to pick a better Index to enhance the performance.

Thanks in advance.

 

[BNPMike]

Try

http://www.csst-technologies.com/testdrv.htm

 

[kkitt]

Thanks for the link, but the software used will allow me to tell how long a query ran, but not the indexes themself.

What I am looking for is a way to tell which indexes were used in a particular query.

Say this query is using 5 tables that have both single and mutliple field indexs setup along with relationships between some of the tables. some of the tables have less then 100 records while other are in the 1/2 to 1 million range.

What I need to know is which indexs were picked for data access.

tblCustOrders using Index_1
tblShipLocations using Index_2
tblTransCarriers using Primary_Index
.....

With this information then I can fine tune the queries by either:

1) adding additional selection criteia
2) possible removing some selection critria (has help sometimes)
3) adding/dropping an index
4) change datatypes of columns
5) create additonal relationships
6) change the order of the table joins

to allow a better index to be selected and limit the number of records selected to the final result sets for each table, thus making the final result set passed back to the applicaiton smaller.

Any help on this would be appreciated, this is very time comsuming by making a change, testing, change order, testing, ect... Only to find out the original, or first change was the best.

 

[BNPMike]

Sorry that didn't help.

I'm not aware of an EXPLAIN facility, not that that means there isn't one. I've never had a problem with performane so far with Access.

Information on plans is probably held in the Querydef collection so you may get somewhere by surfing using that phrase.

 

[kkitt]

Thanks all for your help, will do some goggle searchs on the "Querydef Collection".

For the most part I have not had any trouble with Access's performance. I have had to fine tune a few queries that were used in on online application, and by just adjusting the order that the tables were joined, one additional selection criteria and changing one column from text to long interger, was able to cut 15 seconds out of the execution.

Now this may not seem like a lot, but when this query is used to pull back customer order history by customer specified specific date ranges, and they do it over and over and over again. This does make a difference. Now multiply that by 100's of customer logged on, now a big difference.

Access does comes with a way to do a "Performance Anaylsis" and it did come up with a few sugesstion (add an additional index, create a relationship, ect..) that were implement and did decrease the run time before my changes were made.

I have found that some of the tables have had columns/relationship/indexs created, but the querys were never looked at changed/updated.

I have been very successful by looking at the table/index defs and adjusting the querys just was looking for a way to see if Access and myself were coming to the same conclusion.

 

[KenReay]

Hi

Have not tried any of this but it looks like it could be of interest to you

About ShowPlan
The ShowPlan option was added to Jet 3.0, and produces a text file that contains the query's plan. (ShowPlan doesn't support subqueries.) You must enable it by adding a Debug key to the registry like so:
\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug

Under the new Debug key, add a string data type named JETSHOWPLAN (you must use all uppercase letters). Then, add the key value ON to enable the feature. If Access has been running in the background, you must close it and relaunch it for the function to work.

When ShowPlan is enabled, Jet creates a text file named SHOWPLAN.OUT (which might end up in your My Documents folder or the current default folder, depending on the version of Jet you're using) every time Jet compiles a query. You can then view this text file for clues to how Jet is running your queries. We recommend that you disable this feature by changing the key's value to OFF unless you're specifically using it. Jet appends the plan to an existing file and eventually, the process actually slows things down. Turn on the feature only when you need to review a specific query plan. Open the database, run the query, and then disable the feature.

link is

http://uk.builder.com/architecture/db/0,39026552,20277979,00.htm

I got it by searching google on Jet Query Optimisation

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[BNPMike]

Good work Ken.

 

[kkitt]

Thanks Ken and a Two stars to you...

This is what I was looking for and for those interested I have included a sample of the query and the ShowPlan output.

Query

SELECT DISTINCT tblServiceLevels.SLDesc, tblServiceLevels.SLMinDays, tblServiceLevels.SLMaxDays 

FROM tblServiceLevels, tblSHPTypes INNER JOIN (tblShipFromLocations INNER JOIN (tblCarriers INNER JOIN tblZipCodeDest ON tblCarriers.CarrIndex = tblZipCodeDest.ZipCodeCarrier) ON 
           tblShipFromLocations.LocIndex = tblZipCodeDest.ZipCodeOrg) ON tblSHPTypes.SHPTypeIndex = tblCarriers.CarrShpType

WHERE (tblShipFromLocations.LocName="Austin" Or tblShipFromLocations.LocName="Nashville") 
     AND tblZipCodeDest.ZipCodeDest=501 
     AND tblSHPTypes.SHPTypeName="Bulk" 
    AND tblZipCodeDest.ZipCodeTransitTime<>0
    And tblZipCodeDest.ZipCodeTransitTime<=[SLMaxDays]

ORDER BY tblServiceLevels.SLMinDays, tblServiceLevels.SLMaxDays;

ShowPlan

--- temp query ---

- Inputs to Query -
Table 'tblServiceLevels'
Table 'tblSHPTypes'
    Using index 'PrimaryKey'
    Having Indexes:
    PrimaryKey 2 entries, 1 page, 2 values
      which has 1 column, fixed, unique, clustered and/or counter, primary-key, no-nulls
Table 'tblShipFromLocations'
    Using index 'LocIndex'
    Having Indexes:
    LocIndex 2 entries, 1 page, 2 values
      which has 1 column, fixed, clustered and/or counter
    Loc_ZipCode 1 entrie, 1 page, 1 value
      which has 1 column, fixed
    Loc_Desc 1 entrie, 1 page, 1 value
      which has 1 column, fixed
Table 'tblCarriers'
    Using index 'CarrIndex'
    Having Indexes:
    CarrIndex 6 entries, 1 page, 6 values
      which has 1 column, fixed, unique, clustered and/or counter, primary-key, no-nulls
Table 'tblZipCodeDest'
- End inputs to Query -

01) Restrict rows of table tblZipCodeDest
      using rushmore
      for expression "tblZipCodeDest.ZipCodeDest=501"
      then test expression "Not tblZipCodeDest.ZipCodeTransitTime=0"
02) Inner Join result of '01)' to table 'tblCarriers'
      using index 'tblCarriers!CarrIndex'
      join expression "tblZipCodeDest.ZipCodeCarrier=tblCarriers.CarrIndex"
03) Inner Join result of '02)' to table 'tblSHPTypes'
      using index 'tblSHPTypes!PrimaryKey'
      join expression "tblCarriers.CarrShpType=tblSHPTypes.SHPTypeIndex"
      then test expression "tblSHPTypes.SHPTypeName="Bulk""
04) Inner Join result of '03)' to table 'tblShipFromLocations'
      using index 'tblShipFromLocations!LocIndex'
      join expression "tblZipCodeDest.ZipCodeOrg=tblShipFromLocations.LocIndex"
      then test expression "tblShipFromLocations.LocName In ("Austin","Nashville")"
05) Inner Join result of '04)' to table 'tblServiceLevels'
      using X-Prod join
      then test expression "tblZipCodeDest.ZipCodeTransitTime<=[SLMaxDays]"
06) Sort Distinct result of '05)'

 

[BNPMike]

In case you're not aware "using rushmore" means using indexes. This is where it gets two or more indexes and then plays with them before setting off to retrieve any data pages.