BTW: Just found out that a delta import is only supported by the
SqlEntityProcessor .
Does it matter that I defined processor="CachedSqlEntityProcessor"?
On Tue, Jul 2, 2013 at 5:58 PM, Mysurf Mail <stammail...@gmail.com> wrote:
> I have two entities in 1:n relation - PackageVersion and Tag.
> I have configured DIH to use CachedSqlEntityProcessor and everything works
> as planned.
> First, Tag entity is selected using the query attribute. Then the main
> entity.
> Ultra Fast.
>
> Now I am adding the delta import.
> Everything runs and loads, but too slow.
> Looking at the db profiler output i see :
>
> 1. the delta query of the inner entities run first - which is good.
> 2. the delta query of the main entities runs later - which is still
> good.
> 3. deltaImportQuery of the main entity with each of the ID's runs as a
> single select can be improved using "where in" all the result. Is it
> possible?
> 4.
>
> All of the Query attribute of the other tables are running now. This
> is bad. (In real life I have more than one table in 1:n connection). for
> instance I get a lot of
>
> select ResourceId,[Text] PackageTag
> from [dbo].[Tag] Tag
> Where ResourceType = 0
>
>
> run. Because it is from the Query attribute - there is no where clause for
> using the ids.
> a. How can I fix it ?
> b. Can I translate the importquery to use "where in"
> c. There is no real order for all the select when requesting deltaImport.
> is it possible to implement the caching also when updating delta?
>
> Here is my configuration
>
> <entity name="PackageVersion" pk="PackageVersionId"
> query= "select ....
> from [dbo].[Package] Package inner join
> [dbo].[PackageVersion] PackageVersion on Package.Id =
> PackageVersion.PackageId"
> deltaQuery = "select PackageVersion.Id PackageVersionId
> from [dbo].[Package] Package inner join
> [dbo].[PackageVersion] PackageVersion on Package.Id = PackageVersion.PackageId
> where Package.LastModificationTime >
> '${dataimporter.last_index_time}' OR PackageVersion.Timestamp >
> '${dih.last_index_time}'"
> deltaImportQuery=" select ....
> from [dbo].[Package] Package inner join
> [dbo].[PackageVersion] PackageVersion on Package.Id = PackageVersion.PackageId
> Where PackageVersion.Id='${dih.delta.PackageVersionId}'" >
>
> <entity name="PackageTag" pk="ResourceId"
> processor="CachedSqlEntityProcessor" cacheKey="ResourceId"
> cacheLookup="PackageVersion.PackageId"
> query="select ResourceId,[Text] PackageTag
> from [dbo].[Tag] Tag
> Where ResourceType = 0"
> deltaQuery="select ResourceId,[Text] PackageTag
> from [dbo].[Tag] Tag
> Where ResourceType = 0 and Tag.TimeStamp
> > '${dih.last_index_time}'"
> parentDeltaQuery="select
> PackageVersion.PackageVersionId
> from [dbo].[Package]
> where
> Package.Id=${PackageTag.ResourceId}">
> </entity>
> </entity>
>
>
