We are filling an SQL database/datawarehouse for an SSAS cube starting with PIOLEDB queries and here is the memory used by PIOLEDBENTAgent64 when running queries :
and still growing. At the end the memory was at 2,259,450 K (after 10 minutes running) !
The query :
SELECT eh.Path + eh.Name Element, eh.Name as PotCode, tc.Time as Timestamp, tc.* FROM [ADQ SMART Manufacturing].[Asset].[ElementHierarchy] eh INNER JOIN [ADQ SMART Manufacturing].[DataT].[ft_PotroomCube] tc ON tc.ElementID = eh.ElementID WHERE eh.Path = '\ADQ\Electrolyse\L1\' and eh.Name = '%POTCODE%' AND tc.StartTime = DATE('t-%DAYBACK%d') AND tc.EndTime = DATE('t') AND tc.TimeStep = '1d' OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
Now for each "pot" we have (264 pots) we run this query.
We replace %POTCODE% with the pot name and replace %DAYBACK% with 5d , we want 1 value per day.
tc.* is 115 attributes (measures) coming from ft_PotroomCube who is a transpose table of an AF template (pot template).
I have tried a lot of way to do that .. the HIGH MEMORY problem was always there. This is the fastest way I've found. Never come back when I ask for all pots at the same time.
Maybe we are doing it wrong ? any suggestion will be appreciated.