Use dataimporthandler cache with stored procedures

[Jean-Philippe Quéméner <je...@gmail.com>]

Hi,
I'm trying to import my data from an sql database using the
dataimporthandler. For some nested entity I want to use the cache to cache
the result of my stored procedure. My config looks like this

<document>
>    <entity name="product" dataSource="datawarehouse" query="CALL getSolrProducts();" transformer="RegexTransformer">
>       <field column="pk" name="pk"/>
>       ....
>       <field column="brand_id" name="productbrand"/>
>       <entity name="brand" dataSource="backend" query="CALL getBrandMeta(${product.brand_id});" cacheKey="id" cacheLookup="product.brand_id" cacheImpl="SortedMapBackedCache">
>         <field column="productbrandname" name="productbrandname"/>
>         <field column="productbrandurl" name="productbrandurl"/>
>         <field column="productbrandimage" name="productbrandimage"/>
>         <field column="productbranddata" name="productbranddata"/>
>       </entity>
>       ....
>     </entity>
>   </document>
>
>
For some reason the cache only save the first result and doesn't show the
entity in all other documents having a different brand_id.

I have around 300 different brand_id's so I would expect at least 300
queries to the database but only the first one is made

Am I missing something? .

Re: Use dataimporthandler cache with stored procedures

[Mikhail Khludnev <mk...@griddynamics.com>]

Hello Jean-Philippe,

You either call it 300 times with the different param value, without cache
<entity name="brand" dataSource="backend" query="CALL
getBrandMeta(${product.brand_id});">

or load all rows once and cache them
 <entity name="brand" dataSource="backend" query="CALL getALLBrandMeta();"
cacheKey="id" cacheLookup="product.brand_id"
cacheImpl="SortedMapBackedCache">

SQL examples in the doc explain this clear, I suppose.

On Wed, Nov 25, 2015 at 2:27 PM, Jean-Philippe Quéméner <
jeanphilippe.quemener@gmail.com> wrote:

> Hi,
> I'm trying to import my data from an sql database using the
> dataimporthandler. For some nested entity I want to use the cache to cache
> the result of my stored procedure. My config looks like this
>
> <document>
> >    <entity name="product" dataSource="datawarehouse" query="CALL
> getSolrProducts();" transformer="RegexTransformer">
> >       <field column="pk" name="pk"/>
> >       ....
> >       <field column="brand_id" name="productbrand"/>
> >       <entity name="brand" dataSource="backend" query="CALL
> getBrandMeta(${product.brand_id});" cacheKey="id"
> cacheLookup="product.brand_id" cacheImpl="SortedMapBackedCache">
> >         <field column="productbrandname" name="productbrandname"/>
> >         <field column="productbrandurl" name="productbrandurl"/>
> >         <field column="productbrandimage" name="productbrandimage"/>
> >         <field column="productbranddata" name="productbranddata"/>
> >       </entity>
> >       ....
> >     </entity>
> >   </document>
> >
> >
> For some reason the cache only save the first result and doesn't show the
> entity in all other documents having a different brand_id.
>
> I have around 300 different brand_id's so I would expect at least 300
> queries to the database but only the first one is made
>
> Am I missing something? .
>



-- 
Sincerely yours
Mikhail Khludnev
Principal Engineer,
Grid Dynamics

<http://www.griddynamics.com>
<mk...@griddynamics.com>