Thanks Ben. IRIS upgrade cannot become a dependency for S3 write. We will ensure we revisit these connectors post upgrade.

Hi Robert, 

Thanks for this article. 

How you would go about creating a materialised view for an existing SQL view, which joins quite a lot of tables (combination of inner / left and right joins) to produce it's output? 

We have an existing view in our cache application that takes around 4 hours to produce it's output (it joins around 20/30 different tables and outputs about 300 fields). 4 hours is after having undertaken performance optimisations like adding indexes and tuning tables. Our application has a very high throughput of data.

I am working on creating a materialised version of my aforementioned view and wondering if I have to add data indexes to each of the 20 tables or if there is better way?

Will appreciate any code you might be able to share.


Apologies for the delay in responding but just to say, appreciate your response. This is exactly how we fixed it. 

We did consider swizzling but don't think that applies in this case since since we are inserting child records using ChildRelationship.Insert(childObj) as opposed to swizzling them into memory that they need to be unswizzled later.

@Wolf Koelling, I have been researching this because we encountered <Store> errors when processing data stored within a parent child relationship. <Store> errors occurred because of child data being loaded into memory. This breached default memory allocated to a cache process of 262,144 Kbytes (262 MB).

We initially increased cache process memory using $ZSTORAGE to get rid of <Store> errors and kept doing this till we had increased it to 2048,000 Kbytes (2 GB)! We will be refactoring our code to use one-many relationship instead, as soon as possible.

My take away from this is that it's better to stay away from parent-child relationships and use one-many relationships by default.

and I guess for those that don't want to dig deeper, an easier approach to using SELECT * FROM..., is to use custom class queries.

Hi @Dan Pasco 

This greatly helps my understanding. It would be nice if this information formed part of the documentation for Cache itself and could be elaborated upon.

Essentially to use SELECT, function must implement Table-Valued Function (TVF) interface. Is there any specific documentation relative to TVF you are able to point me towards please?