cancel
Showing results for 
Search instead for 
Did you mean: 

powerbuilder 12.6 classic using an access database

Former Member
0 Kudos

How can I tell if my Datawindows in 12.6 are actual using the indexes that I have built on my tables. Or to reinstate in a different way, how do I know if I chose the right fields or combination of fields to optimize the indexed fields between a database and an application?

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Philip;

1) Turn on SQL Tracing.

SQLCA.DBMS = "trace <db client>

- or -

Use Mark's suggestion.

2) Open the DB Painter

3) Paste the SQL into the iSQL pane & add a ';' at the end.

4) Use the RHMB & select " EXPLAIN " from the resulting popup menu.

5) you should now see the SQL "plan". Have a look & see if the index you expected was used.

HTH

Regards ... Chris

Former Member
0 Kudos

Here is how you turn on Show Plan in Access:

Open Regedit and go to this key:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines

Add a new key named 'Debug'

Add a new string under 'Debug' named 'JETSHOWPLAN'

Set the value of 'JETSHOWPLAN' to 'ON'

Now paste the SQL from your DataWindow into a SQL window in Access and run it.

Access will write information to a text file named 'showplan.out'. The file will either be in your 'My Documents' folder or in the same folder as the database.

To turn Show Plan off, change the value of 'JETSHOWPLAN' to 'OFF'.

This is where I found out about this:

http://www.techrepublic.com/article/use-microsoft-jets-showplan-to-write-more-efficient-queries/

Former Member
0 Kudos

Hey Philip,

Capture the SQL in your sqlpreview event and run it on your db in your sql editor. I don't know what statement you use on Access to turn "showplan" on but google should be able to help.

hth,

Mark