Integration with external enterprise fulltext search engine

[Former Member]

We have c++ app based on SQL Anywhere 12, and for some bigger reports we want to speed up search times.

We are thinking to integrate with such search servers like Apache Solr, Elasticsearch or Sphinx Search - anyone tried this? Is it possible with SQL Anywhere? Can such engines connect to SQL Anywhere and make indexes?

[VolkerBarth]

Can't tell - just to ask: Have you considered to use the builtin SA fulltext search facility?

[Former Member]

Yes, but we are looking for something faster than SA fulltext search.

[Vlad]

It is not a complete answer, that is why I have decided to put this into comment. What I know from the indexing world (I saw some solutions that are not related to SA) is the following:

1. One solution was using Apache Solr for indexing. The custom application (the server indeed) was using "background threads" that were preparing data from MySql and sending it to Solr via REST API that Solr provides. The developer defined the Solr document called Product, and defined some fields that the server should index. Then a separate thread was selecting DB records from multiple tables (using the "last_update_time" in the WHERE predicate for obvious reasons) and sending these records as JSON to Solr. Every user request was sent to Solr, and the server returned primary keys back that were using to fetch records from the MySql database).

2. Another solution that I see is the Sphinx engine that is able to pull data from SA using ODBC driver (I have googled a bit for you: http://sphinxsearch.com/docs/current.html#conf-odbc-dsn). I guess that Sphinx is working on the similar way that every indexer works - there is a process that executes SELECTs via ODBC and fetching data that should be indexed. Everything what the user provides is the fields to be indexed.

Of course this is just my assumption how I would implement the solution using a stand-alone indexer. There can be different solutions from a default component that the search server provides to fetch records using ODBC/JDBC, to some "event handlers/triggers/SqlAnywhere Scheduled Events" where you push your data to the searching engine.

An interesting question how to get this data back with minimum efforts. If the searching engine can expose its records via ODBC, then you can try to use SA proxy tables. Otherwise, you have to use API what the engine provides.