Sequential filtering in SQL

[Ed Kleban]

On 12/12/05 2:52 AM, "Ruslan Zasukhin" <sunshine at public.kherson.ua> wrote:

> On 12/11/05 11:34 PM, "Ed Kleban" <Ed at Kleban.com> wrote:
> 
> Hi Ed,
> 
>> Ok Ruslan,
>> 
>> Time for a brand new topic.
>> 
>> I starting to work on the part of the design that will use the bit_and( x, y
>> ) operator you have provided to allow me to do some masked filtering.  I
>> need to apply multiple filters in multiple separate passes to take a big
>> list and make it smaller and smaller with each filter pass until I have only
>> the records I want remaining.
>> 
>> If bit_and were available as an API call, this would be very simple.
>> I would simply do a Find to get a VSet, then do more Finds using the
>> previous VSet result as an "inSet" argument for the next filter pass.
> 
> I think
> 
> Table Methods = Calculated fields is an answer.
> You make some Method, setup its formula which return e.g. True/false
> Do 
> 
>   set1 = fldMethod.FindValue( 1 )
> 
> Change method, and do next search.

Aha!  I think you are right :)  And it looks like a very good answer!

So help me understand this a bit better.  Unfortunately other than
mentioning that that calculated fields exist and noting how to define, them
neither the Kernel nor the V4RB Reference provide any significant
documentation describing calculated fields.

When I define a calculated field in the API, and then perform a FindValue
for example on that field, the interface is essentially taking my method and
passing it over to the SQL engine somehow behind my back, getting an answer,
and returning a VSet somehow?

Or does Valentina have a native expression analyzer that can interpret the
MethodText, render it into code for the calulcated field, and uses it for
Find commands without going through SQL?

> This will be not indexed searches.

Of course, I'm not expecting them to be.  I'm getting the speed from
applying the searches sequentially and thus reducing the number of records
that need to be searched each time.

> For super fast SEARCH you can have several methods which are indexed.
> In other words, you have pre-calculation in your hands.



On 12/12/05 2:54 AM, "Ruslan Zasukhin" <sunshine at public.kherson.ua> wrote:

> 
>> So... Unless you have a recommendation as to something more efficient I
>> might try, my only option at this time seems to be the "manually build a new
>> table with a loop from a Vcursor" technique for every level of filter I want
>> to use.
> 
> About SQL. Why you cannot make SQL query which combine all your required
> filters by AND 
> 
>     WHERE bit_and(f1,..) AND bit_and() AND bit_and ...

One answer is: Because my (possibly incorrect now that I think about it)
assumption was that this would have the worst performance of all possible
solutions because it would search the entire table 3 times.  I can see now
that it wouldn't have to but rather should do the exact sequential filtering
I want if WHERE is implemented properly.  Makes sense.

But a better answer is:
 
> ... I need to keep the
> result of the first filter (the outside WHERE) around in memory so that I
> can [later use] 
> one of many different second-level filters on this smaller list at any
> time.  So nesting WHERE statements does not get me what I need.

In other words I want to get the result of the first WHERE bit_and(f1,...)
and keep it around because I want to use that a the smaller set of records
to search for many different second-level refinement searches.

That make sense?