How to tell what index tags are actually used

Hello all and thank in advance for the help.

I have an app that I maintain which I inherited from other coders. There are
some tables for which over 50 index tags have been created and I'm certain
that not all of them are needed. I can certainly tell which ones are
referred to in the code by searching for all of the instances of SET ORDER
in the project but that still leaves me with over 30 that may be
unnecessary.

Is there anything I can turn on that is similar to profiling that will tell
me which indexes actually get used?

I suppose that I could always remove them one at a time and see if anyone
complains that their report or query has gotten really slow but I would
prefer a more intelligent approach.

Does anyone have any ideas?

Thanks again.

Jeff

David
Fri Dec 03 10:06:10 CST 2004

Jeff,

You can go through the code looking for all the SQL SELECT statements
looking for the JOINing and FILTERing conditions. You should also look for
any SET FILTER, REPLACE and SCAN statements.

--
df - Microsoft MVP FoxPro http://www.geocities.com/df_foxpro

"Jeff Grippe" <jgrippe@hilldun.com> wrote in message
news:10r0q71gjh883a3@news.supernews.com...
> Hello all and thank in advance for the help.
>
> I have an app that I maintain which I inherited from other coders. There
> are some tables for which over 50 index tags have been created and I'm
> certain that not all of them are needed. I can certainly tell which ones
> are referred to in the code by searching for all of the instances of SET
> ORDER in the project but that still leaves me with over 30 that may be
> unnecessary.
>
> Is there anything I can turn on that is similar to profiling that will
> tell me which indexes actually get used?
>
> I suppose that I could always remove them one at a time and see if anyone
> complains that their report or query has gotten really slow but I would
> prefer a more intelligent approach.
>
> Does anyone have any ideas?

tom
Tue Dec 07 05:04:47 CST 2004

Hi,

I would add a small application method oapp.logit() which "Echos" (Set alter
to or so) ALIAS() , Key() and TAG() into a logfile and place calls to this
method allover the programs. Then just let the users use it for some days
und put the textfile into excel and sort/filter it. I would not use a dbf
for it to prevent sideeffects, just produce a textfile.
May be you put asserts into it but then you have to use it by yourself.

HTH
Tom


"Jeff Grippe" <jgrippe@hilldun.com> schrieb im Newsbeitrag
news:10r0q71gjh883a3@news.supernews.com...
> Hello all and thank in advance for the help.
>
> I have an app that I maintain which I inherited from other coders. There
are
> some tables for which over 50 index tags have been created and I'm certain
> that not all of them are needed. I can certainly tell which ones are
> referred to in the code by searching for all of the instances of SET ORDER
> in the project but that still leaves me with over 30 that may be
> unnecessary.
>
> Is there anything I can turn on that is similar to profiling that will
tell
> me which indexes actually get used?
>
> I suppose that I could always remove them one at a time and see if anyone
> complains that their report or query has gotten really slow but I would
> prefer a more intelligent approach.
>
> Does anyone have any ideas?
>
> Thanks again.
>
> Jeff
>
>

Jeff
Tue Dec 07 07:32:06 CST 2004

Actually those aren't the ones that are hard to find. A grep through the
code picks up the ones that are a result of coding. The ones that are harder
to find are the ones that were created to optimize report and query
generation.

Thanks
"tom knauf" <tom.knauf@itds.de> wrote in message
news:cp42og$24d$05$1@news.t-online.com...
> Hi,
>
> I would add a small application method oapp.logit() which "Echos" (Set
> alter
> to or so) ALIAS() , Key() and TAG() into a logfile and place calls to
> this
> method allover the programs. Then just let the users use it for some days
> und put the textfile into excel and sort/filter it. I would not use a dbf
> for it to prevent sideeffects, just produce a textfile.
> May be you put asserts into it but then you have to use it by yourself.
>
> HTH
> Tom
>
>
> "Jeff Grippe" <jgrippe@hilldun.com> schrieb im Newsbeitrag
> news:10r0q71gjh883a3@news.supernews.com...
>> Hello all and thank in advance for the help.
>>
>> I have an app that I maintain which I inherited from other coders. There
> are
>> some tables for which over 50 index tags have been created and I'm
>> certain
>> that not all of them are needed. I can certainly tell which ones are
>> referred to in the code by searching for all of the instances of SET
>> ORDER
>> in the project but that still leaves me with over 30 that may be
>> unnecessary.
>>
>> Is there anything I can turn on that is similar to profiling that will
> tell
>> me which indexes actually get used?
>>
>> I suppose that I could always remove them one at a time and see if anyone
>> complains that their report or query has gotten really slow but I would
>> prefer a more intelligent approach.
>>
>> Does anyone have any ideas?
>>
>> Thanks again.
>>
>> Jeff
>>
>>
>
>

John
Tue Dec 07 09:34:58 CST 2004

Hey Jeff,

If making a few code changes isn't a problem (assuming you don't have just
an exe or something) you may want to take a look at placing some code around
queries using sys(3054), which measures optimization and what tags are
employed.

HTH,

John

"Jeff Grippe" <jgrippe@hilldun.com> wrote in message
news:10r0q71gjh883a3@news.supernews.com...
> Hello all and thank in advance for the help.
>
> I have an app that I maintain which I inherited from other coders. There
are
> some tables for which over 50 index tags have been created and I'm certain
> that not all of them are needed. I can certainly tell which ones are
> referred to in the code by searching for all of the instances of SET ORDER
> in the project but that still leaves me with over 30 that may be
> unnecessary.
>
> Is there anything I can turn on that is similar to profiling that will
tell
> me which indexes actually get used?
>
> I suppose that I could always remove them one at a time and see if anyone
> complains that their report or query has gotten really slow but I would
> prefer a more intelligent approach.
>
> Does anyone have any ideas?
>
> Thanks again.
>
> Jeff
>
>

tom
Wed Dec 08 05:22:23 CST 2004

Hi,

I added that code to the "on exit" of the report summary to find out that a
damn "wizard_2" was built.
On filelevel I watched my app with filemon from sysinternals.

Tom



"Jeff Grippe" <jgrippe@hilldun.com> schrieb im Newsbeitrag
news:10rbbr23fqpv243@news.supernews.com...
> Actually those aren't the ones that are hard to find. A grep through the
> code picks up the ones that are a result of coding. The ones that are
harder
> to find are the ones that were created to optimize report and query
> generation.
>
> Thanks
> "tom knauf" <tom.knauf@itds.de> wrote in message
> news:cp42og$24d$05$1@news.t-online.com...
> > Hi,
> >
> > I would add a small application method oapp.logit() which "Echos" (Set
> > alter
> > to or so) ALIAS() , Key() and TAG() into a logfile and place calls to
> > this
> > method allover the programs. Then just let the users use it for some
days
> > und put the textfile into excel and sort/filter it. I would not use a
dbf
> > for it to prevent sideeffects, just produce a textfile.
> > May be you put asserts into it but then you have to use it by yourself.
> >
> > HTH
> > Tom
> >
> >
> > "Jeff Grippe" <jgrippe@hilldun.com> schrieb im Newsbeitrag
> > news:10r0q71gjh883a3@news.supernews.com...
> >> Hello all and thank in advance for the help.
> >>
> >> I have an app that I maintain which I inherited from other coders.
There
> > are
> >> some tables for which over 50 index tags have been created and I'm
> >> certain
> >> that not all of them are needed. I can certainly tell which ones are
> >> referred to in the code by searching for all of the instances of SET
> >> ORDER
> >> in the project but that still leaves me with over 30 that may be
> >> unnecessary.
> >>
> >> Is there anything I can turn on that is similar to profiling that will
> > tell
> >> me which indexes actually get used?
> >>
> >> I suppose that I could always remove them one at a time and see if
anyone
> >> complains that their report or query has gotten really slow but I would
> >> prefer a more intelligent approach.
> >>
> >> Does anyone have any ideas?
> >>
> >> Thanks again.
> >>
> >> Jeff
> >>
> >>
> >
> >
>
>