Alaska Software Inc. - Filter optimization and index keys
Alaska Software Inc. - Filter optimization and index keys
Username: Password:
AuthorTopic: Filter optimization and index keys
mirda Filter optimization and index keys
on Mon, 10 Oct 2011 17:55:43 +0200
Hi

I'm migrating one of my clipper projects into alaska xbase. During
migration and testing I've noticed significant slow down in my reports.
In clipper I've used Six library and it's query optimizer was working
perfectly.
For example:
order1 key: field1 + field2 + field3
order2 key: field2 + field1 + field3
current order is order1. 
I set scope for this order and set filter field2="abc". 
Six query optimizer recognize this filter and optimize search, while Alaska
don't recognize this filter as optimizable expression. 

Should I add order3 with only field2 in key  
or
should I use expression field2 + field1 + field3 = "abc" in my filter?

What is your suggestion?

I use Alaska Xbase v1.90.355 SL1, FOXDBE & CDXDBE

Thanks for answers in advance.

Damir
James Loughner Re: Filter optimization and index keys
on Mon, 10 Oct 2011 12:06:07 -0400
Not sure why you need Filter at all. Order 2 in your example would work 
setting scope on "abc"+current scope on Order 1

You might also check the Rushmore and Optimize and related settings in 
doc's.

Jim

On 10/10/2011 11:55 AM, mirda wrote:
> Hi
>
> I'm migrating one of my clipper projects into alaska xbase. During
> migration and testing I've noticed significant slow down in my reports.
> In clipper I've used Six library and it's query optimizer was working
> perfectly.
> For example:
> order1 key: field1 + field2 + field3
> order2 key: field2 + field1 + field3
> current order is order1.
> I set scope for this order and set filter field2="abc".
> Six query optimizer recognize this filter and optimize search, while Alaska
> don't recognize this filter as optimizable expression.
>
> Should I add order3 with only field2 in key
> or
> should I use expression field2 + field1 + field3 = "abc" in my filter?
>
> What is your suggestion?
>
> I use Alaska Xbase v1.90.355 SL1, FOXDBE&  CDXDBE
>
> Thanks for answers in advance.
>
> Damir
mirda Re: Filter optimization and index keys
on Mon, 10 Oct 2011 18:31:54 +0200
Hi Jim!

Let's say that field1 is FirstName and field2 is LastName.
I need, for example, all last names which begins with "abc" and all first
names which begins with "def".
I set order to order1 (key field1 + field2 -> FirstName + LastName) and set
scope to "def".
Then I set filter to field2 = "abc" (LastName="abc")

Damir

On Mon, 10 Oct 2011 12:06:07 -0400, James Loughner wrote:

> Not sure why you need Filter at all. Order 2 in your example would work 
> setting scope on "abc"+current scope on Order 1
> 
> You might also check the Rushmore and Optimize and related settings in 
> doc's.
> 
> Jim
> 
> On 10/10/2011 11:55 AM, mirda wrote:
>> Hi
>>
>> I'm migrating one of my clipper projects into alaska xbase. During
>> migration and testing I've noticed significant slow down in my reports.
>> In clipper I've used Six library and it's query optimizer was working
>> perfectly.
>> For example:
>> order1 key: field1 + field2 + field3
>> order2 key: field2 + field1 + field3
>> current order is order1.
>> I set scope for this order and set filter field2="abc".
>> Six query optimizer recognize this filter and optimize search, while Alaska
>> don't recognize this filter as optimizable expression.
>>
>> Should I add order3 with only field2 in key
>> or
>> should I use expression field2 + field1 + field3 = "abc" in my filter?
>>
>> What is your suggestion?
>>
>> I use Alaska Xbase v1.90.355 SL1, FOXDBE&  CDXDBE
>>
>> Thanks for answers in advance.
>>
>> Damir
James Loughner Re: Filter optimization and index keys
on Mon, 10 Oct 2011 16:43:36 -0400
Well Filter is not as good as Six's. There are several optimization 
settings that may help as I said.

Essentially Filter just skips records. If the system as not seen a 
record yet it must fetch it then skip. Takes time. You may want to look 
at WILD CARD SEARCHES. Setting Filter after Scope will reduce the number 
of misses and speed things a bit. I try to avoid Filters if I can. The 
other speed up is to go to a database engine so the filter takes place 
at the server.

Jim


On 10/10/2011 12:31 PM, mirda wrote:
> Hi Jim!
>
> Let's say that field1 is FirstName and field2 is LastName.
> I need, for example, all last names which begins with "abc" and all first
> names which begins with "def".
> I set order to order1 (key field1 + field2 ->  FirstName + LastName) and set
> scope to "def".
> Then I set filter to field2 = "abc" (LastName="abc")
>
> Damir
>
> On Mon, 10 Oct 2011 12:06:07 -0400, James Loughner wrote:
>
>> Not sure why you need Filter at all. Order 2 in your example would work
>> setting scope on "abc"+current scope on Order 1
>>
>> You might also check the Rushmore and Optimize and related settings in
>> doc's.
>>
>> Jim
>>
>> On 10/10/2011 11:55 AM, mirda wrote:
>>> Hi
>>>
>>> I'm migrating one of my clipper projects into alaska xbase. During
>>> migration and testing I've noticed significant slow down in my reports.
>>> In clipper I've used Six library and it's query optimizer was working
>>> perfectly.
>>> For example:
>>> order1 key: field1 + field2 + field3
>>> order2 key: field2 + field1 + field3
>>> current order is order1.
>>> I set scope for this order and set filter field2="abc".
>>> Six query optimizer recognize this filter and optimize search, while Alaska
>>> don't recognize this filter as optimizable expression.
>>>
>>> Should I add order3 with only field2 in key
>>> or
>>> should I use expression field2 + field1 + field3 = "abc" in my filter?
>>>
>>> What is your suggestion?
>>>
>>> I use Alaska Xbase v1.90.355 SL1, FOXDBE&   CDXDBE
>>>
>>> Thanks for answers in advance.
>>>
>>> Damir
mirda Re: Filter optimization and index keys
on Mon, 17 Oct 2011 09:19:48 +0200
Thank you, Jim.

Damir


On Mon, 10 Oct 2011 16:43:36 -0400, James Loughner wrote:

> Well Filter is not as good as Six's. There are several optimization 
> settings that may help as I said.
> 
> Essentially Filter just skips records. If the system as not seen a 
> record yet it must fetch it then skip. Takes time. You may want to look 
> at WILD CARD SEARCHES. Setting Filter after Scope will reduce the number 
> of misses and speed things a bit. I try to avoid Filters if I can. The 
> other speed up is to go to a database engine so the filter takes place 
> at the server.
> 
> Jim
> 
> 
> On 10/10/2011 12:31 PM, mirda wrote:
>> Hi Jim!
>>
>> Let's say that field1 is FirstName and field2 is LastName.
>> I need, for example, all last names which begins with "abc" and all first
>> names which begins with "def".
>> I set order to order1 (key field1 + field2 ->  FirstName + LastName) and set
>> scope to "def".
>> Then I set filter to field2 = "abc" (LastName="abc")
>>
>> Damir
>>
>> On Mon, 10 Oct 2011 12:06:07 -0400, James Loughner wrote:
>>
>>> Not sure why you need Filter at all. Order 2 in your example would work
>>> setting scope on "abc"+current scope on Order 1
>>>
>>> You might also check the Rushmore and Optimize and related settings in
>>> doc's.
>>>
>>> Jim
>>>
>>> On 10/10/2011 11:55 AM, mirda wrote:
>>>> Hi
>>>>
>>>> I'm migrating one of my clipper projects into alaska xbase. During
>>>> migration and testing I've noticed significant slow down in my reports.
>>>> In clipper I've used Six library and it's query optimizer was working
>>>> perfectly.
>>>> For example:
>>>> order1 key: field1 + field2 + field3
>>>> order2 key: field2 + field1 + field3
>>>> current order is order1.
>>>> I set scope for this order and set filter field2="abc".
>>>> Six query optimizer recognize this filter and optimize search, while Alaska
>>>> don't recognize this filter as optimizable expression.
>>>>
>>>> Should I add order3 with only field2 in key
>>>> or
>>>> should I use expression field2 + field1 + field3 = "abc" in my filter?
>>>>
>>>> What is your suggestion?
>>>>
>>>> I use Alaska Xbase v1.90.355 SL1, FOXDBE&   CDXDBE
>>>>
>>>> Thanks for answers in advance.
>>>>
>>>> Damir