[SQL] emulating sub-queries and search prformances

[Antoine Beyeler]

Hi,


>> UPDATE table SET mark = false WHERE mark = true
>
>     I think better just "UPDATE t SET mark false"
>     you avoid search.
>


According to some preliminary test with ViSQL, the "WHERE mark = true" 
is much faster if only a few record are marked (like a tenth of the 
total rec count), and this is a correct assumption in my case.

>>
>> Do you think that the search with marked records could be improved??
>
> Yes, this is known issue.
> Yes this can be improved.

ok.

>
>> I think so, since the search on WHERE mark=true is _very_ fast (less 
>> than a
>> 10th of sec on my setup) and returns far less records than the total 
>> count
>> (199 vs 18000). But it seems that this fact is not taken in account 
>> in your
>> search.
>
> Antoine, it seems you use C++. So
>
> 1) why you not use BitSet class?
> yes, it can be little more work for you, but result may surprise you!
>
> 2) again, when you do
>
>     BitSet1 = do search on 'old'
>     Bitset2 = do search on 'man'
>
>     bitset3 = BitSet1 intersection BitSet2
>
> Now you can SELF iterate found 199 records and check them on 'old 
> main'.
> Also you can use here Formula to iterate records of bitSet.
>
> Try this.
>
> Steve, once have mention that BitSets can give speed up to 100 times
> comparing to SQL.
>


this seems most interesting to me, and I'll investigate.

Best regards,
Antoine