seek

I have tables with compound indexes. Performing a seek seems to require the
padding of the fields (at least the first field of the index). The index in
not based on the padding. Am I missing something? Do I need to pad the
variables I am seeking?

Dan
Fri Aug 19 17:06:06 CDT 2005

Beats the heck out of me. <g>

What is your index expression and what expression are you seeking?

Dan

Cowboy wrote:
> I have tables with compound indexes. Performing a seek seems to
> require the padding of the fields (at least the first field of the
> index). The index in not based on the padding. Am I missing
> something? Do I need to pad the variables I am seeking?

lew
Fri Aug 19 17:16:54 CDT 2005

Yes: the variables you are seeking need to be formatted to match the column
widths of the underlying table. Format them so the actual seek'd expression
matches the index expression. This way rushmore will kick in.

eg
If you have an index on name + address, don't try
seek padr(m.name,len(name))+padr(m.address,len(address)) && doesn't
match the index expression
rather
m.name = padr(m.name,len(name))
m.address = padr(m.address,len(address))
seek m.name + m.address && matching expression

Much of this does not matter if you use varchars in VFP 9.

-Lew
"Cowboy" <Cowboy@discussions.microsoft.com> wrote in message
news:A3BD78D8-29CE-49E9-90C8-D427C75C9D4F@microsoft.com...
>I have tables with compound indexes. Performing a seek seems to require
>the
> padding of the fields (at least the first field of the index). The index
> in
> not based on the padding. Am I missing something? Do I need to pad the
> variables I am seeking?

Cowboy
Fri Aug 19 17:22:04 CDT 2005

I have a field named cStyleCode and is c(6) plus cGarment c(10). The style
code contents may have any number of characters up to 6 and the Garment field
may have any number of characters up to 10. I find that if I index on the
first field (cStyleCode) with the trim function (alltrim(cStyleCode
+dGarment), I can do the seek sucessfully. Alternatively, if I pad the
padr(cStyleCode, 06), the seek also works. I don't like the latter in the
event I ever change the size of the cStyleCode field, I would then have to
change the seek padr...since the 06 is then hardcoded. Indexing on the
alltrimof the first field is ok but I would rather omit the alltrim function.
ALso, I suspect if the index has more than the two fields, I would have to
consider the above with the second field, etc.

"Dan Freeman" wrote:

> Beats the heck out of me. <g>
>
> What is your index expression and what expression are you seeking?
>
> Dan
>
> Cowboy wrote:
> > I have tables with compound indexes. Performing a seek seems to
> > require the padding of the fields (at least the first field of the
> > index). The index in not based on the padding. Am I missing
> > something? Do I need to pad the variables I am seeking?
>
>
>

Ook
Fri Aug 19 17:17:11 CDT 2005

The index is on a charactor field? SET EXACT is ON?

"Cowboy" <Cowboy@discussions.microsoft.com> wrote in message
news:A3BD78D8-29CE-49E9-90C8-D427C75C9D4F@microsoft.com...
>I have tables with compound indexes. Performing a seek seems to require
>the
> padding of the fields (at least the first field of the index). The index
> in
> not based on the padding. Am I missing something? Do I need to pad the
> variables I am seeking?

Rush
Fri Aug 19 17:36:52 CDT 2005

Fox ignores the TRIM in the index field - all fields are indexed with their
full width. Use the PADR() function, but with the FSIZE() function instead
of a hard-coded value.

Also, look into your SET EXACT settings.

- Rush

"Cowboy" <Cowboy@discussions.microsoft.com> wrote in message
news:15B761BE-C45C-42EA-A5FE-F97CD1DB012D@microsoft.com...
>I have a field named cStyleCode and is c(6) plus cGarment c(10). The style
> code contents may have any number of characters up to 6 and the Garment
> field
> may have any number of characters up to 10. I find that if I index on the
> first field (cStyleCode) with the trim function (alltrim(cStyleCode
> +dGarment), I can do the seek sucessfully. Alternatively, if I pad the
> padr(cStyleCode, 06), the seek also works. I don't like the latter in the
> event I ever change the size of the cStyleCode field, I would then have to
> change the seek padr...since the 06 is then hardcoded. Indexing on the
> alltrimof the first field is ok but I would rather omit the alltrim
> function.
> ALso, I suspect if the index has more than the two fields, I would have to
> consider the above with the second field, etc.
>
> "Dan Freeman" wrote:
>
>> Beats the heck out of me. <g>
>>
>> What is your index expression and what expression are you seeking?
>>
>> Dan
>>
>> Cowboy wrote:
>> > I have tables with compound indexes. Performing a seek seems to
>> > require the padding of the fields (at least the first field of the
>> > index). The index in not based on the padding. Am I missing
>> > something? Do I need to pad the variables I am seeking?
>>
>>
>>

Cowboy
Fri Aug 19 17:34:02 CDT 2005

I'm confused. I don't understand how
m.name = padr(m.name,len(name))
m.address = padr(m.address,len(address))
seek m.name + m.address && matching expression
is different from
seek padr(m.name,len(name))+padr(m.address,len(address))

"lew" wrote:

> Yes: the variables you are seeking need to be formatted to match the column
> widths of the underlying table. Format them so the actual seek'd expression
> matches the index expression. This way rushmore will kick in.
>
> eg
> If you have an index on name + address, don't try
> seek padr(m.name,len(name))+padr(m.address,len(address)) && doesn't
> match the index expression
> rather
> m.name = padr(m.name,len(name))
> m.address = padr(m.address,len(address))
> seek m.name + m.address && matching expression
>
> Much of this does not matter if you use varchars in VFP 9.
>
> -Lew
> "Cowboy" <Cowboy@discussions.microsoft.com> wrote in message
> news:A3BD78D8-29CE-49E9-90C8-D427C75C9D4F@microsoft.com...
> >I have tables with compound indexes. Performing a seek seems to require
> >the
> > padding of the fields (at least the first field of the index). The index
> > in
> > not based on the padding. Am I missing something? Do I need to pad the
> > variables I am seeking?
>
>
>

lew
Fri Aug 19 23:02:27 CDT 2005

Rushmore recognizes the *expression*, not the result.
"Cowboy" <Cowboy@discussions.microsoft.com> wrote in message
news:E7467808-3E5F-49A4-8F06-33EE19928134@microsoft.com...
> I'm confused. I don't understand how
> m.name = padr(m.name,len(name))
> m.address = padr(m.address,len(address))
> seek m.name + m.address && matching expression
> is different from
> seek padr(m.name,len(name))+padr(m.address,len(address))
>
> "lew" wrote:
>
>> Yes: the variables you are seeking need to be formatted to match the
>> column
>> widths of the underlying table. Format them so the actual seek'd
>> expression
>> matches the index expression. This way rushmore will kick in.
>>
>> eg
>> If you have an index on name + address, don't try
>> seek padr(m.name,len(name))+padr(m.address,len(address)) && doesn't
>> match the index expression
>> rather
>> m.name = padr(m.name,len(name))
>> m.address = padr(m.address,len(address))
>> seek m.name + m.address && matching expression
>>
>> Much of this does not matter if you use varchars in VFP 9.
>>
>> -Lew
>> "Cowboy" <Cowboy@discussions.microsoft.com> wrote in message
>> news:A3BD78D8-29CE-49E9-90C8-D427C75C9D4F@microsoft.com...
>> >I have tables with compound indexes. Performing a seek seems to require
>> >the
>> > padding of the fields (at least the first field of the index). The
>> > index
>> > in
>> > not based on the padding. Am I missing something? Do I need to pad
>> > the
>> > variables I am seeking?
>>
>>
>>

Bernhard
Sat Aug 20 09:42:56 CDT 2005

Hi lew
> Rushmore recognizes the *expression*, not the result.
That's true, but not with SEEK.
In
LOCATE FOR expression = value
the expression should match the index expression. For the value part it is not
necessary to match the index expression.
In a SEEK there is only this value part, the expression part is "hidden" in the
seek command and always matches the index expression.

Regards
Bernhard Sander

Rush
Sat Aug 20 10:02:19 CDT 2005

You can index on a raw memo field:

INDEX ON 'X' + MemoField TAG MemoField

but I don't know how Fox treats that key internally.

[Can't say I've had a need to know , , ,]

- Rush

"Paul Pedersen" <no-reply@swen.com> wrote in message
news:O7%23ZbPSpFHA.3064@TK2MSFTNGP15.phx.gbl...
>
> "Rush Strong" <rush.strong]@[verizon.net> wrote in message
> news:%23zvTu2QpFHA.3036@TK2MSFTNGP14.phx.gbl...
>> Fox ignores the TRIM in the index field
>
> It has to, because variable length keys are not supported. That's why you
> can't index on memo fields (although you can index on a fixed-length
> substring of a memo field).
>
>
>

Paul
Sat Aug 20 10:35:11 CDT 2005


"Rush Strong" <rush.strong]@[verizon.net> wrote in message
news:u1gEZdZpFHA.2952@TK2MSFTNGP15.phx.gbl...
> You can index on a raw memo field:
>
> INDEX ON 'X' + MemoField TAG MemoField
>
> but I don't know how Fox treats that key internally.

Could be it just pads it to length 240 (max key length).


> [Can't say I've had a need to know , , ,]

Probably not. If you have something important enough to index, it probably
shouldn't be in a memo field anyway!

Cindy
Sat Aug 20 11:48:24 CDT 2005

Hi Cowboy,

"cStyleCode and is c(6) plus cGarment c(10). "

Whether or not you do any trimming, Fox will always pad the index expression
to the full width because indexes are fixed width just like fields. If you
index on "Alltrim(cStyleCode) + cGarment" then Fox will effectively use
'PadR(Alltrim(cStyleCode) + cGarment, 16)" as the index expression.

Set Exact, Set Ansi, and Set Near determine how Fox will match index
expressions, for example seeking "abc" against "abcdef". Check out these
topics in Help.

Trimming can give unpredictable results making "abcd " + "efgh " look the
same as "abcde " + "fgh ". It's better to stay away from trimming in index
expressions.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden


"Cowboy" <Cowboy@discussions.microsoft.com> wrote in message
news:A3BD78D8-29CE-49E9-90C8-D427C75C9D4F@microsoft.com...
>I have tables with compound indexes. Performing a seek seems to require
>the
> padding of the fields (at least the first field of the index). The index
> in
> not based on the padding. Am I missing something? Do I need to pad the
> variables I am seeking?