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>
