Solr - Is there a way to speed up my import

Question

I have a relational database model This is the basics of my data-config.xml

<entity name="MyMainEntity" pk="pID" query="select ... from [dbo].[TableA] inner join TableB on ...">
    <entity name="Entity1" pk="Id1" query="SELECT [Text] Tag from [Table2] where ResourceId = '${MyMainEntity.pId}'"></entity>
            <entity name="Entity1" pk="Id2" query="SELECT [Text] Tag from [Table2] where ResourceId2 = '${MyMainEntity.pId}'"></entity>
    <entity name="LibraryItem" pk="ResourceId" 
            query="select SKU
                    FROM [TableB] 
                    INNER JOIN ...
                    ON ...
                    INNER JOIN ...
                    ON ...
                    WHERE ... AND ...'">
    </entity>
</entity>

Now, this takes a lot of time.
10000 rows in the first query and then each other inner entities are fetched later (around 10 rows each).

If I use a db profiler I see a the three inner entities query running over and over (3 select sentences than again 3 select sentences over and over)
This is really not efficient.
And the import can run over 40 hrs ()
Now,
What are my options to run it faster .

Obviously there is an option to flat the tables to one big table - but that will create a lot of other side effects. I would really like to avoid that extra effort and run solr on my production relational tables.
So far it works great out of the box and I am searching here if there is a configuration tweak.
If I will flat the rows that - does the schema.xml need to be change too? or the same fields that are multivalued will keep being multivalued.

Thanks.

If they are single sub entity, how about building a view over the tables instead of firing multiple queries ? that is much much faster — Jayendra, Jun 27 '13 at 09:02
Generating a view will force me to generate another primary key (because of the multiplication) I would like to avoid it as much as I can — Bick, Jun 27 '13 at 10:19

score 1 · Accepted Answer · answered Jun 27 '13 at 07:20

without changing the schema of the DB, the first thing to try is caching. If the inner entities cache well, gains will be substantial.

Maybe the wiki is not uptodate so you should check the jira issues, namely solr-2382 and maybe have a look at solr-2948 too.

A second path could be trying multithreading DIH, but it's more tricky. At one point this was optional, but later was removed cause it was buggy, and I think now there was some jira issue trying to reimplement it, try look it up, but I recommend caching first.

Solr - Is there a way to speed up my import

1 Answers1