lucene-java-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Phil brunet" <>
Subject Re: Mixing database and lucene searches
Date Wed, 12 May 2004 12:17:25 GMT
> > -- Snip --
> > If you can't guaranty a fixed number of Lucene results (and it is often 
>the > case !), a good way is to duplicate the last PK and so to  round to a 
> > number.
> >
>Hi... I'm not sure what you mean by that last bit.

Hi ... i'm going to try to express myself correctly ... in english :-)

We were talking about the need to cross Lucene results and DB results. And 
that it could be a good idea to execute a query like :

FROM my_table
       <1st criteria >         // this criteria was not expressed in the 
Lucene query
AND <2nd criteria>         // this criteria was not expressed in the Lucene 

AND  ....
AND my_pk IN (pk_value_1, pk_value_2, .... pk_value_n);

where pk_values have been previously retrieved by the Lucene query.

In the JDBC statement, using bind variables is a good way to avoid useless 
query parsing time.

But if the number of "pk_value" retrieved by the Lucene query is different 
for each query, using bind variables will not avoid the query parsing time. 
Because the SQL "query signature" will be differente, so the rdbms will need 
to parse the query again.

To bypass this problem, you can "round" the number of b ind variable.

For exemple, you know that your Lucene queries will retrieve ... let's say 
... a maximum of 1000 results.
Sometimes only one result is retieved => you have one pk_value
Sometime  5 results are retrieved       => you have five pk_value
Sometime .... etc

I suggest that in each case, you duplicate the last pk_value in order to 
have always the same number of bind variables in the SQL statement.

In my exemple, you will always have 1000 bind variables in the SQL 
statement, whatever you had one, five or n results.

Especially for short SQL queries, avoiding parsing time is really precious  
(i work with Oracle DB- sic !)

MSN Search, le moteur de recherche qui pense comme vous !

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message