[closed]About the SQL and Performance

[BIsrik]

Hi,

Ã,  in crystal reports or in impromptu some calculations are done at the report level itself. therefore there is some sharing of load between the tool and the database.

Is there any way to achieve this type of executing techniques in reportnet???

because If i use an if-else clause or any functions in the report it gets translated to sql. Then when i run the sql all the calculation takes place at the database which increases the load on the database.

Srik

[sir_jeroen]

In Report Studio you can set processing to:
Database only or Limited Local...

You can find it in the properties under Query Explorer

[BIsrik]

How will the load sharing take place. Because when i see the sql with or without local processing enabled they are same...

Srik

[CoginAustin]

Are you looking at the cognos SQL? I believe this just makes it so the Database does not have to apply all the aggregation rules and instead cognos calculates it.  Or, maybe I am thinking rollup processing. I've never found a need to use either.

[BIsrik]

i have a requirement here wherein there are around 90 conditions in a if clause and around 15 calculations and some another 5 which uses some of the 15 calculations. When i see the sql it sounds alrming. thats i am looking for some load balancing b/w reportnet and the database.

The database  i am using here is oracle.

Srik

[cognosfreelancer]

That many if then else statements will tire the SQL...

Why don't you consider splitting your SQL statement into multiple tabular models and then using a tabular set to merge the individual tabular models.

NKT

[BIsrik]

i don't think i can use t.set here. because my if - else statement would produce numbers depending on the conditions. these numbers are then used in those 15 calculation and some of these 15 are used in some other calculation.

In crystal only the columns are picked and there is no computation done at the database level. the calculation is done at the report level. thus there results, some load balancing between database and reporting tool.

I am just trying to achieve something similar to this...

Srik

[BIsrik]

I came to know about a feature server adminstration in reportnet. Went through documents but couldn't get any information on that... Can anyone tell what these things are, how to use them, or some documents where i can get these information.

Srik

[sir_jeroen]

I don't think that's the place where you should look...

Settings there will be applied to the reportnet server and not to your database server and if i understood you correctly your problem is with the sql processing and not the CRN server.  That's all I can think of :S

[BIsrik]

 i want to share the sql load b/w CRN server and the database server.
I exactly don't know what the server adminstration does. I thought by doing some tuning there it might help someway. Any help for this...

Srik

[BIsrik]

I was reading the FM document, i found that FM creates SQL of two types. Minimized and View. I could get the explanation of minimized way of SQL but couldn't get what he says for view.

Here is the text he says regarding the sql types:

" The SQL Generation type of a query subject can be set to either As View or Minimized. By
default, it is set to Minimized.
When the generation type is set to Minimized, the generated SQL contains only the minimal set of
tables and joins needed to obtain values for the selected query items.
When the generation type is set to As View, Framework Manager generates queries that contain
the full SQL statement that defined the query subject. Use As View when you want to ensure that
the query is run as a block. The SQL is treated as a view. For example, you want the query to
return the same number of rows each time it is run. "

Please let me how would this be useful for performance tuning...

Srik

[Cornelis]

I was reading the FM document, i found that FM creates SQL of two types. Minimized and View. I could get the explanation of minimized way of SQL but couldn't get what he says for view.

Here is the text he says regarding the sql types:

" The SQL Generation type of a query subject can be set to either As View or Minimized. By
default, it is set to Minimized.
When the generation type is set to Minimized, the generated SQL contains only the minimal set of
tables and joins needed to obtain values for the selected query items.
When the generation type is set to As View, Framework Manager generates queries that contain
the full SQL statement that defined the query subject. Use As View when you want to ensure that
the query is run as a block. The SQL is treated as a view. For example, you want the query to
return the same number of rows each time it is run. "

Please let me how would this be useful for performance tuning...

Srik

This will not help you..
The way this works is that if you create a model query subject consisting of 3 tables (e.g. Product-ProductType-ProductLine) and you create a report using only one table (e.g. a prompt showing all products), the behaviour between 'as view' and 'minimized' will differ:

as view - entire query subject is executed (all 3 tables)
minimized - since we only use product information, only this table is used in the SQL.

Minimized is the default setting.
When would you use the 'as view' setting? One example would be when you include security in your model query subject. You connect a table containing userid's and things that those users are allowed to see to the real data.
Because you always would like to apply this security, you have to set the 'as view' setting.

Can't help you out with your original request, Reportnet will normally try to push as much work as possible to the database, and I'm not sure if you can tune this.

regards,
Cornelis

[sir_jeroen]

Little reminder: close the topic if it's been solved/closed (see board rules)

[BIsrik]

if u look at the explaination of cognos "For example, you want the query to
return the same number of rows each time it is run. "

Can it help in this cases...

I have 2 calculation based on the user input...These calculation are then used in some other calculation. if i set to the sql generation as view...will the first 2 calculation values be computed once and the value passed to the other calculation...

Srik

[BIsrik]

the thing thats gets always in my mind is why can't there be a check in the RN wherein it will see whether the report spec is valid or not..Everytime u run the report the spec is validated...In my case it almost takes an hour to validate and then prompt for values. Is the architecture of the product hindering these changes...

Srik

[sir_jeroen]

Little reminder: close the topic if it's been solved/closed (see board rules)

[BIsrik]

Came to know about composite information server of cognos wherein we can cache the data for the calculation. Doing some testing with it...

Cognos says this is used for complex computing and to increase ur performance...

If any of u have already worked on this or have a knowledge on this plz share with us..

Srik