[SQL] emulating sub-queries and search prformances

[Ruslan Zasukhin]

on 3/28/03 6:07 PM, Antoine Beyeler at globule at vtx.ch wrote:

Hi Antoine,

> As I understand it, sub-queries are not implemented yet. I devised a
> way to work around that, but it is not working well and may illustrate
> a possibility to further improve search performances of Valentina.
> 
> In short, I'm trying to make indexed text search with a multi-word
> query, like 'old man', and I tried this approach:
> (Mark is a boolean field. Text is a VDK_Text field. I use method to
> achieve case-unsensitiveness, but it is not illustrated here for the
> sake of simplicity.)
> 
> UPDATE table SET mark = false WHERE mark = true
    
    I think better just "UPDATE t SET mark false"
    you avoid search.

> UPDATE table SET mark = true WHERE text = 'old'
> UPDATE table SET mark = false WHERE mark = true AND NOT text = 'man'
> 
> At this point, all records that contains 'old' and 'man' are "marked"
> to true. This process take much less than 1 sec on a 18000 records 200
> MB database. My idea was that such preprocessing would greatly increase
> performance for this linear search:
> 
> SELECT * FROM table WHERE mark = true AND text LIKE 'old man'
> 
> With my database, I have 199 records marked as true, and 138
> effectively containing 'old man', but the above query takes exactly the
> same time that this one (that is about 6 sec):
> 
> SELECT * FROM table WHERE text LIKE 'old man'
> 
> In short, the preprocessing is useless.
> 
> Do you think that the search with marked records could be improved??

Yes, this is known issue.
Yes this can be improved.

> 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. 

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://listserv.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------