Which one is better option ?

I am using Visual Foxpro 8.0. In my application i have two panes, on left
pane i have product categories and on right pane respective products are
being shown.
Currently i am using FILTER command to show respective items. But it is
getting slow and slow as data is increasing. Right now i have more than
10,000 records in products table and system is not responding quick enough.
There is another way to do same job using INDEX command with FOR condition.
Before going in any optimization work, i would like to have your expert
opinion which option can work better for such kind of requirements. OR any
other new suggestion.

Thanks in advance.

NR

Stefan
Tue May 30 08:42:40 CDT 2006


"NR" <NR@discussions.microsoft.com> schrieb im Newsbeitrag
news:AEB332BA-7B20-4CCF-BB15-0A7429360401@microsoft.com...
>I am using Visual Foxpro 8.0. In my application i have two panes, on left
> pane i have product categories and on right pane respective products are
> being shown.
> Currently i am using FILTER command to show respective items. But it is
> getting slow and slow as data is increasing. Right now i have more than
> 10,000 records in products table and system is not responding quick enough.
> There is another way to do same job using INDEX command with FOR condition.
> Before going in any optimization work, i would like to have your expert
> opinion which option can work better for such kind of requirements. OR any
> other new suggestion.

If you want to keep the Set Filter approach, upgrading to Vfp9 can
be an option. Have a look a the new grid.Optimize property there.

Otherwise, better use parameterized (local) views as your grid's
RecordSource. You'll find a lot about them in Vfp's help and also
a bunch of threads about them in these groups' archives via
http://groups.google.com/advanced_group_search
See also Sys(3054) in help.


hth
-Stefan

Man-wai
Tue May 30 09:00:42 CDT 2006

> Currently i am using FILTER command to show respective items. But it is
> getting slow and slow as data is increasing. Right now i have more than
> 10,000 records in products table and system is not responding quick enough.

Make use of Rushmore. Build an index on the fields of the PRODUCT table
that needed to be searched by the Category panel.

--
.~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Ubuntu 5.10) Linux 2.6.16.18
^ ^ 21:59:03 up 7 days 6:59 0 users load average: 1.06 1.16 1.24
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

NR
Wed May 31 00:08:01 CDT 2006

I already have an index on product table and i am sure Rushmore technology
would be working with FILTER command.
Any thing else i can try?

"Man-wai Chang" wrote:

> > Currently i am using FILTER command to show respective items. But it is
> > getting slow and slow as data is increasing. Right now i have more than
> > 10,000 records in products table and system is not responding quick enough.
>
> Make use of Rushmore. Build an index on the fields of the PRODUCT table
> that needed to be searched by the Category panel.
>
> --
> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Ubuntu 5.10) Linux 2.6.16.18
> ^ ^ 21:59:03 up 7 days 6:59 0 users load average: 1.06 1.16 1.24
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk
>

Imaginecorp
Wed May 31 00:33:19 CDT 2006

My approach would be to never use a filter. filters are clunky and tend to
crap out with large datasets. Create Indexes on the appropriate fields, set
the index off then do a Select....(Rushmore works better with no indexes
set.) Check if Select (query) is optimized with sys(3054).
10,000 records is nothing. Once it reaches 100,000 or so periodically sort
the table by the most common query.
good luck
Mohammed
www.imaginecorp.com/whatwedo.htm

"NR" <NR@discussions.microsoft.com> wrote in message
news:C6BF2E9C-963D-4C93-96ED-E631FFC9F296@microsoft.com...
>I already have an index on product table and i am sure Rushmore technology
> would be working with FILTER command.
> Any thing else i can try?
>
> "Man-wai Chang" wrote:
>
>> > Currently i am using FILTER command to show respective items. But it is
>> > getting slow and slow as data is increasing. Right now i have more than
>> > 10,000 records in products table and system is not responding quick
>> > enough.
>>
>> Make use of Rushmore. Build an index on the fields of the PRODUCT table
>> that needed to be searched by the Category panel.
>>
>> --
>> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
>> / v \ Simplicity is Beauty! May the Force and Farce be with you!
>> /( _ )\ (Ubuntu 5.10) Linux 2.6.16.18
>> ^ ^ 21:59:03 up 7 days 6:59 0 users load average: 1.06 1.16 1.24
>> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk
>>

NR
Wed May 31 00:51:01 CDT 2006

Thanks Imaginecorp, for your sugession. I think this is much better approach.
I am thankful to all of you who has given me his precious time.
If there is any more idea please do share with me.

"Imaginecorp" wrote:

> My approach would be to never use a filter. filters are clunky and tend to
> crap out with large datasets. Create Indexes on the appropriate fields, set
> the index off then do a Select....(Rushmore works better with no indexes
> set.) Check if Select (query) is optimized with sys(3054).
> 10,000 records is nothing. Once it reaches 100,000 or so periodically sort
> the table by the most common query.
> good luck
> Mohammed
> www.imaginecorp.com/whatwedo.htm
>
> "NR" <NR@discussions.microsoft.com> wrote in message
> news:C6BF2E9C-963D-4C93-96ED-E631FFC9F296@microsoft.com...
> >I already have an index on product table and i am sure Rushmore technology
> > would be working with FILTER command.
> > Any thing else i can try?
> >
> > "Man-wai Chang" wrote:
> >
> >> > Currently i am using FILTER command to show respective items. But it is
> >> > getting slow and slow as data is increasing. Right now i have more than
> >> > 10,000 records in products table and system is not responding quick
> >> > enough.
> >>
> >> Make use of Rushmore. Build an index on the fields of the PRODUCT table
> >> that needed to be searched by the Category panel.
> >>
> >> --
> >> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> >> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> >> /( _ )\ (Ubuntu 5.10) Linux 2.6.16.18
> >> ^ ^ 21:59:03 up 7 days 6:59 0 users load average: 1.06 1.16 1.24
> >> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk
> >>
>
>
>

christophe
Wed May 31 02:13:12 CDT 2006

NR,

There is one more option (not the best, but maybe the easiest)

Set Key To

can only be used with indexed filterexpressions.

regards
christophe

"NR" <NR@discussions.microsoft.com> schreef in bericht
news:51D6A7A2-9031-4416-AEEF-D021B1F9F744@microsoft.com...
> Thanks Imaginecorp, for your sugession. I think this is much better
approach.
> I am thankful to all of you who has given me his precious time.
> If there is any more idea please do share with me.
>
> "Imaginecorp" wrote:
>
> > My approach would be to never use a filter. filters are clunky and tend
to
> > crap out with large datasets. Create Indexes on the appropriate fields,
set
> > the index off then do a Select....(Rushmore works better with no indexes
> > set.) Check if Select (query) is optimized with sys(3054).
> > 10,000 records is nothing. Once it reaches 100,000 or so periodically
sort
> > the table by the most common query.
> > good luck
> > Mohammed
> > www.imaginecorp.com/whatwedo.htm
> >
> > "NR" <NR@discussions.microsoft.com> wrote in message
> > news:C6BF2E9C-963D-4C93-96ED-E631FFC9F296@microsoft.com...
> > >I already have an index on product table and i am sure Rushmore
technology
> > > would be working with FILTER command.
> > > Any thing else i can try?
> > >
> > > "Man-wai Chang" wrote:
> > >
> > >> > Currently i am using FILTER command to show respective items. But
it is
> > >> > getting slow and slow as data is increasing. Right now i have more
than
> > >> > 10,000 records in products table and system is not responding quick
> > >> > enough.
> > >>
> > >> Make use of Rushmore. Build an index on the fields of the PRODUCT
table
> > >> that needed to be searched by the Category panel.
> > >>
> > >> --
> > >> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> > >> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> > >> /( _ )\ (Ubuntu 5.10) Linux 2.6.16.18
> > >> ^ ^ 21:59:03 up 7 days 6:59 0 users load average: 1.06 1.16
1.24
> > >> news://news.3home.net news://news.hkpcug.org
news://news.newsgroup.com.hk
> > >>
> >
> >
> >

NR
Wed May 31 03:31:02 CDT 2006

I am not sure, does it use rushmore technology. In VFP help i couldn't see
rushmore working with SET KEY command. As this command is purely based on set
INDEX so it might be using rushmore.. but i am not sure ..... :( ?

Anyways thanks for helping me.

"christophe" wrote:

> NR,
>
> There is one more option (not the best, but maybe the easiest)
>
> Set Key To
>
> can only be used with indexed filterexpressions.
>
> regards
> christophe
>
> "NR" <NR@discussions.microsoft.com> schreef in bericht
> news:51D6A7A2-9031-4416-AEEF-D021B1F9F744@microsoft.com...
> > Thanks Imaginecorp, for your sugession. I think this is much better
> approach.
> > I am thankful to all of you who has given me his precious time.
> > If there is any more idea please do share with me.
> >
> > "Imaginecorp" wrote:
> >
> > > My approach would be to never use a filter. filters are clunky and tend
> to
> > > crap out with large datasets. Create Indexes on the appropriate fields,
> set
> > > the index off then do a Select....(Rushmore works better with no indexes
> > > set.) Check if Select (query) is optimized with sys(3054).
> > > 10,000 records is nothing. Once it reaches 100,000 or so periodically
> sort
> > > the table by the most common query.
> > > good luck
> > > Mohammed
> > > www.imaginecorp.com/whatwedo.htm
> > >
> > > "NR" <NR@discussions.microsoft.com> wrote in message
> > > news:C6BF2E9C-963D-4C93-96ED-E631FFC9F296@microsoft.com...
> > > >I already have an index on product table and i am sure Rushmore
> technology
> > > > would be working with FILTER command.
> > > > Any thing else i can try?
> > > >
> > > > "Man-wai Chang" wrote:
> > > >
> > > >> > Currently i am using FILTER command to show respective items. But
> it is
> > > >> > getting slow and slow as data is increasing. Right now i have more
> than
> > > >> > 10,000 records in products table and system is not responding quick
> > > >> > enough.
> > > >>
> > > >> Make use of Rushmore. Build an index on the fields of the PRODUCT
> table
> > > >> that needed to be searched by the Category panel.
> > > >>
> > > >> --
> > > >> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> > > >> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> > > >> /( _ )\ (Ubuntu 5.10) Linux 2.6.16.18
> > > >> ^ ^ 21:59:03 up 7 days 6:59 0 users load average: 1.06 1.16
> 1.24
> > > >> news://news.3home.net news://news.hkpcug.org
> news://news.newsgroup.com.hk
> > > >>
> > >
> > >
> > >
>
>
>
>

Andrew
Wed May 31 05:07:47 CDT 2006

"NR" <NR@discussions.microsoft.com> wrote in message
news:1A02CEE5-F296-4310-832A-43B557965243@microsoft.com...
>I am not sure, does it use rushmore technology. In VFP help i couldn't see
> rushmore working with SET KEY command. As this command is purely based on
> set
> INDEX so it might be using rushmore.. but i am not sure ..... :( ?

Last time I had anything to do with SET KEY it was the fastest way to filter
a record set, period.

--
regards
Andrew Howell

NR
Wed May 31 05:45:01 CDT 2006

Yeah! You are right. It is fastest command to filter data but there is a
problem in my scenario i.e. When i get data filtered using SET KEY command
and afterwards i cannot show records ordered by product description. As you
know index would be set on product codes and results to be shown in
description order so that end user can quickly locate his desired item.
By the way is there any option in data grid to show results ordered by a
specific column.


"Andrew Howell" wrote:

> "NR" <NR@discussions.microsoft.com> wrote in message
> news:1A02CEE5-F296-4310-832A-43B557965243@microsoft.com...
> >I am not sure, does it use rushmore technology. In VFP help i couldn't see
> > rushmore working with SET KEY command. As this command is purely based on
> > set
> > INDEX so it might be using rushmore.. but i am not sure ..... :( ?
>
> Last time I had anything to do with SET KEY it was the fastest way to filter
> a record set, period.
>
> --
> regards
> Andrew Howell
>
>
>
>

Olaf
Wed May 31 05:49:23 CDT 2006

>I am not sure, does it use rushmore technology. In VFP help i couldn't see
> rushmore working with SET KEY command. As this command is purely based on
> set
> INDEX so it might be using rushmore.. but i am not sure ..... :( ?

Rushmore is just the thing that find's out what index to use
to optimize performance.

SET KEY TO doesn't needs to be rushmore optimized,
it uses the index you set, so it has not to figure out, what
index to use. The same goes with SEEK and LOOKUP.

But as Stefan Wuebbe said, Filtered Tables are okay with
vfp9's new grid property .optimize.

Bye, Olaf.

NR
Wed May 31 06:02:02 CDT 2006

I have an idea but want your expert opinion on it.
Idea is. If on every change in category, i re-issue INDEX command with FOR
condition to filter respective data as it uses rushmore technology so i feel
it should work better then SET FILTER.

One more thing, I have already tried i.e. i have used variable in FOR
condition whiel creating INDEX but every time after setting order i need to
REINDEX table and the creepiest thing is. It re-creates all indexes of active
CDX. So do you suggest re-issuing INDEX command on every change?

If i go for beneath suggestion, i will have to do lot of maintenance in my
existing application.
If Indexing again & again doesn't make any difference i will prefer it.

Every one is requested to please give your valuable inputs on this issue.

Thanks & Regards,
NR

"Imaginecorp" wrote:

> My approach would be to never use a filter. filters are clunky and tend to
> crap out with large datasets. Create Indexes on the appropriate fields, set
> the index off then do a Select....(Rushmore works better with no indexes
> set.) Check if Select (query) is optimized with sys(3054).
> 10,000 records is nothing. Once it reaches 100,000 or so periodically sort
> the table by the most common query.
> good luck
> Mohammed
> www.imaginecorp.com/whatwedo.htm
>

Olaf
Wed May 31 07:15:29 CDT 2006

> One more thing, I have already tried i.e. i have used variable in FOR
> condition whiel creating INDEX but every time after setting order i need
> to
> REINDEX table and the creepiest thing is. It re-creates all indexes of
> active
> CDX. So do you suggest re-issuing INDEX command on every change?

If you want to go that way, you should then create a separate single idx
index locally for the current user. INDEX ON ... TO IDXFilename.

That is larger, as it is not a compact index, but it is local and does
not effect other users.

But that seems like a lot of hassle, if you do something like that,
you may also simply use a View or SQL-Select to get the data set,
you want to display.

Bye, Olaf.

Imaginecorp
Wed May 31 17:33:04 CDT 2006

Hello NR;
I would not use a For condition in a index. its complicated and limits you
to only the For condition and when not used often it will slow the addition
of records as it now has to reindex another tag . Just create straight field
indexes.

If you were setting the filter on say products based upon category. lets say
you have 2 tables Category has a field "Type" which holds "Tires", products
has a "Brand" field which holds "Goodyear", "Ceat", etc .

Create an index on Category.type , product.brand and one on their common key
Next set the index off for both tables

Set ansi on
{do not set exact on, ansi and exact have a conflict IMO}
cString = ""
cString = " and category.Type = 'Tires' "
{Make sure you have Spaces between the quotes and the characters.}

Select category.type, products.brand from category, products ;
where category.key = product.key &cString ;
into cursor .....

that's it.
Now you can send anything to cString that you want i.e. " and category.type
= ' Trucks ' "
Fine tune it and try it, it will be fast. We use this approach with tables
with over hundreds of thousands of records.
Good luck
Mohammed



"NR" <NR@discussions.microsoft.com> wrote in message
news:A94182A3-56AD-4D22-8062-55287E69E942@microsoft.com...
>I have an idea but want your expert opinion on it.
> Idea is. If on every change in category, i re-issue INDEX command with FOR
> condition to filter respective data as it uses rushmore technology so i
> feel
> it should work better then SET FILTER.
>
> One more thing, I have already tried i.e. i have used variable in FOR
> condition whiel creating INDEX but every time after setting order i need
> to
> REINDEX table and the creepiest thing is. It re-creates all indexes of
> active
> CDX. So do you suggest re-issuing INDEX command on every change?
>
> If i go for beneath suggestion, i will have to do lot of maintenance in my
> existing application.
> If Indexing again & again doesn't make any difference i will prefer it.
>
> Every one is requested to please give your valuable inputs on this issue.
>
> Thanks & Regards,
> NR
>
> "Imaginecorp" wrote:
>
>> My approach would be to never use a filter. filters are clunky and tend
>> to
>> crap out with large datasets. Create Indexes on the appropriate fields,
>> set
>> the index off then do a Select....(Rushmore works better with no indexes
>> set.) Check if Select (query) is optimized with sys(3054).
>> 10,000 records is nothing. Once it reaches 100,000 or so periodically
>> sort
>> the table by the most common query.
>> good luck
>> Mohammed
>> www.imaginecorp.com/whatwedo.htm
>>
>