New post
AllAccepted answers
Abbad Minhas · Jun 26, 2021

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.

Regards

Abbad Minhas · Apr 8, 2020

@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.

Abbad Minhas · Nov 28, 2019

Hi Rupert, 

I hope you are well and enjoying robotics. 

Thanks for coming back to me.

Your recommended approach, though hugely appreciated, won't work for me I'm afraid. 

In answer to your questions:

  • I am making my call from object script, as part of an already built component within a financial cache application that explicitly uses a SELECT statement
  • Changing this component to use CALL instead of a SELECT is not possible due to dependencies and legacy code.

Essentially question I am asking is why some stored procedures can only be called via CALL and why can some be called by both CALL and SELECT (such as in the case of class queries)?

I get a feeling this has something to do with SQL-Invokable-Routine (SIR) - see @Dan Pasco comment here but there is almost nothing in the documentation or any code for me to go further on.

Abbad Minhas · Nov 13, 2019

It seems that views in cache need to start with a "SELECT ..." therefore to get around this, I did the following which works: 

Class DC.NewClass1 [ ClassType = view, ViewQuery = {
SELECT '', As A, '' As B WHERE 1=2

UNION

SELECT A,B FROM DC.ClosedFuturesProc(65257,65286) --'2019-09-01','2019-09-30'
} ]
{ Parameter READONLY = 1; }