chandotnet

PIOLEDB Query will take 2GB of memory and more !

Discussion created by chandotnet on May 16, 2012
Latest reply on Jun 18, 2012 by chandotnet

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 :

 

0550.PIOLEDB_5F00_2GB.PNG

 

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.

 

Sebastien

 

 

Outcomes