Database - quickest way to do this?

[Ruslan Zasukhin]

On 10/6/07 4:13 PM, "David Bovill" <david at openpartnership.net> wrote:

Hi David,

> That should get Ruslan to reply :)
> 
> Keywords are in a simple database link table and i want to do a search for
> handlers based on 10 or so keywords selected by the user or automatically
> selected by the software. Now i want to return a list of hits in descending
> ranking depending on how many keywords are linked to the record - so if all
> the keywords are there (unlikely you'd get the highest ranking). Ideally it
> would be asynchronous with a first raft of result coming quick and the
> broader result added later... but thats a detail. It has to be fast as it
> will work as you are typing if possible.
> 
> So I guess I do an "or" SQL search for all these keywords, and then loop
> through them checking hits. Or do I do "AND" searches, and iterate replacing
> the "AND" with "OR" to widen the search?

1) Actually it is not clear:
    your result should contain OR or AND for words?

          word1 AND word2 ...
or
          word1 OR word2 ...
 
> NB - it would be nice to do a speed test on this trying the following
> options:
> 
>    1. In memory - arrays - can't figure - seems complex?
>    2. Stack with cards and use "find" - old school but could work :)
>    3. sqlLite
>    4. valentina (the fastest of course :)

Not a fact comparing to memory arrays, :-)
but you did not mention size of your table, i.e. How many records.


2) Do you think about LOCAL db, or you need access remote server ?
    it seems if you talk about lists or SqlLite, it is local
    right ?


3) for now I can give this point:
Valentina API (and V4REV) also, has LOW LEVEL API calls.

Using them for this task you can:

---------------------------------------------------
  * do search on word1. Get result S1 as BitSet or ArraySet
        of N1 found records.

  * show to user this SET, and actually show him only first
      10-20 records that fit window's list box

DO LOOP
{

  * search on word, get second SET S2.

  * you can keep all Sn results on your hands if you need,
    or just accumulate result into Sr

        S(r) = S(i) intersect S(i+1)    -- AND
        S(r) = S(i) union S(i+1)        -- OR

  * Update S(r) in the list of Widow asynchronous.
---------------------------------------------------

Above this is the only algorithm to split the whole task into few steps on
low level and get asynchronous execution as you did want.
This works if you not use SQL way, and if you work locally.


4) IF do job carefully, then you can even split this into few threads, and
then many chances that on N CPU computer they will work together.


5) for remote db it is possible write stored procedure on server side.

Let me know if you need more points.



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]