That sounds like a perfect match for
http://wiki.apache.org/solr/DataImportHandler#CachedSqlEntityProcessor :)
On Thursday, May 16, 2013 at 6:01 PM, O. Olson wrote:
> I am using the DataImportHandler to Query a SQL Server and populate Solr.
> Unfortunately, SQL does not have an understanding of hierarchical
> relationships, and hence I use Table Joins. The following is an outline of
> my table structure:
>
>
> PROD_TABLE
> -> SKU (Primary Key)
> -> Title (varchar)
> -> Descr (varchar)
>
> CAT_TABLE
> -> SKU (Foreign Key)
> -> CategoryLevel (int i.e. 1, 2, 3 …)
> -> CategoryName (varchar)
>
> I specify the SQL Query in the db-data-config.xml file – a snippet of which
> looks like:
>
> <dataConfig>
> <dataSource driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
> url="jdbc:sqlserver://localhost\...."/>
> <document>
> <entity name="Product"
> query="SELECT SKU, Title, Descr FROM PROD_TABLE">
> <field column="SKU" name="SKU" />
> <field column="Title" name="Title" />
> <field column="Descr" name="Descr" />
>
> <entity name="Cat1"
> query="SELECT CategoryName from CAT_TABLE where
> SKU='${Product.SKU}' AND CategoryLevel=1">
> <field column="CategoryName" name="Category1" />
> </entity>
> <entity name="Cat2"
> query="SELECT CategoryName from CAT_TABLE where
> SKU='${Product.SKU}' AND CategoryLevel=2">
> <field column="CategoryName" name="Category2" />
> </entity>
> <entity name="Cat3"
> query="SELECT CategoryName from CAT_TABLE where
> SKU='${Product.SKU}' AND CategoryLevel=3">
> <field column="CategoryName" name="Category3" />
> </entity>
>
> </entity>
> </document>
> </dataConfig>
>
> It seems like the DataImportHandler handler sends out three or four queries
> for each Product. This results in a very slow import. Is there any way to
> speed this up? I would not mind an intermediate step of first extracting SQL
> and then putting it into Solr.
>
> Thank you for all your help.
> O. O.
>
>
>
>
> --
> View this message in context:
> http://lucene.472066.n3.nabble.com/Speed-up-import-of-Hierarchical-Data-tp4063924.html
> Sent from the Solr - User mailing list archive at Nabble.com
> (http://Nabble.com).
>
>
