Combine SQL with an external search

Question

In my case SQL for structured data and considering Lucene for text search. Yes MSSQL has FullText but Lucene offers some stuff I want. For the purpose of the question any external search.

In SQL there is a main table with a PK. In SQL there are a number queries that use the main table and number of other tables.

From the external search I will get list of Main.PK to filter by. That list could be from 1 to to 1 million.

The external search is the most expensive part of the search. The SQL part is very efficient. Passing the SQL PK to the external is not really a good option as I need various data from the SQL query. The only thing coming back from Lucene is the PK (term) and some times the score.

Is there a best practice?

Options I see are

where Main.PK in (PK values from external search)
populate the external search PK values in a #TEMP and join to that
since some times I need the score this seems best as I can put the score in the #temp

In an ideal world there would be a join like this:

join exeternalvirtualtable as evt 
  on evt.PK = Main.PK 
 and syntax specific to the external search

I get that is asking a lot but is there anything like that in general?
Is there a syntax/API to make an external search look like a table (or view) to MSSQL?
Is there anything like that for MSSQL to Lucene?
This is kind of a start OLE DB Providers and OPENROWSET
Ideally a .NET Framework Data Providers for Lucene that mapped some SQL syntax to Lucene.
The app is .NET in case there is a .NET specific solution.

The product RavenDB combines a structures and unstructured (Lucene) search very fast even if the Lucene return a lot of row so there has to be a way to do this short of putting PK in a #temp.

vahid · Accepted Answer · 2015-09-18T21:19:01.473

1

Is there a syntax/API to make an external search look like a table (or view) to MSSQL?

You can use IndexSearcher class of Lucene, it will give you a TopDocs object that contain the relevant documents (PKs in your case). Then you can populate a SQL table based on this result.

You will need something like this:

    TopDocs topDocs = searcher.search(query, MAX_HITS);
    for (int i = 0; i < topDocs.scoreDocs.length; i++) {
        Document doc = searcher.doc(topDocs.scoreDocs[i].doc);
        String pk = doc.get("PK");
        // Connection to database and executing insertion 
    }

edited Sep 18 '15 at 21:19

answered Sep 18 '15 at 19:33

vahid

141
7

Thanks. Looks useful. But it does not make an external search look like a table. Still very useful. – paparazzo Sep 18 '15 at 19:41
I have added a code fragment. Hope it will be helpful. – vahid Sep 18 '15 at 21:21
It is very helpful. Still not virtual table. It is how to populate a real table. – paparazzo Sep 18 '15 at 21:36
Looks like a virtual table was too much to ask for. This seems like the best approach. Thanks. Don't get why the question got a down vote but it would not be fair to delete my question to get the down vote to go away. – paparazzo Sep 19 '15 at 16:28

Combine SQL with an external search

1 Answers1