Segmented project & minimized SQL

[Michael75]

I'm working on a large FM project which has been around for a few years. It contains several linked segments, but we almost always open the MASTER segment in FM when there's work to be done. Version is C10.2.1, using DQM against Netezza. All the packages are defined in the MASTER segment.

I'm familiar with the concept of minimised SQL, and always use select * from table when creating a new QS. I noticed a couple of existing QS which used an all columns construct rather than select *. I "corrected" these, and when subsequently performing a test expected to see this message disappear:

<message severity="information"title="XQE Message"type="planningStats">XQE-PLN-0232 Metadata will be retrieved from the database because a query subject with a matching name does not exist. database='TEST_NETEZZA' catalog='' schema='COMMON_DMT' table='my_table_name'

But it's still there!

I did several tests, to try to understand what was going on. I found that some QS in this project do indeed generate minimised SQL. But systematically, it is only those that are defined in the MASTER segment. Here's the high-level structure of the project.



Any QS in the linked EFFECTIF segment that I tested would not generate minimised SQL, whether I opened it via the MASTER, or whether I opened the segment directly in FM.

It really is as black & white as that. Every select * QS in the main segment generates minimised SQL. And no QS in a linked segment does.

I've never seen any mention of this behaviour, and my gut feeling is that it is a bug in 10.2.1.

Would anybody like to comment?

[MFGF]

I'm working on a large FM project which has been around for a few years. It contains several linked segments, but we almost always open the MASTER segment in FM when there's work to be done. Version is C10.2.1, using DQM against Netezza. All the packages are defined in the MASTER segment.

I'm familiar with the concept of minimised SQL, and always use select * from table when creating a new QS. I noticed a couple of existing QS which used an all columns construct rather than select *. I "corrected" these, and when subsequently performing a test expected to see this message disappear:

<message severity="information"title="XQE Message"type="planningStats">XQE-PLN-0232 Metadata will be retrieved from the database because a query subject with a matching name does not exist. database='TEST_NETEZZA' catalog='' schema='COMMON_DMT' table='my_table_name'

But it's still there!

I did several tests, to try to understand what was going on. I found that some QS in this project do indeed generate minimised SQL. But systematically, it is only those that are defined in the MASTER segment. Here's the high-level structure of the project.

https://ibb.co/daH4FQ

Any QS in the linked EFFECTIF segment that I tested would not generate minimised SQL, whether I opened it via the MASTER, or whether I opened the segment directly in FM.

It really is as black & white as that. Every select * QS in the main segment generates minimised SQL. And no QS in a linked segment does.

I've never seen any mention of this behaviour, and my gut feeling is that it is a bug in 10.2.1.

Would anybody like to comment?

Hi,

That doesn't sound right to me. I'd expect to see minimised SQL regardless of whether a project is segmented or not. If I was you I'd log this with IBM support and see what they say.

Cheers!

MF.

[Michael75]

Thanks MF!

I'll do that, if I can work out the admin bit (we go via a 3rd party for support).

Poor / erratic response times in Cognos is a big issue for us. While I appreciate that there can be many other factors at play here, do you, or does anybody else, have an idea of the actual impact of minimised SQL on DB response time? I'm willing to follow just about any avenue to improve response times. But if minimised SQL only has a negligible impact, maybe I won't spend too much time on it.

Thx, Michael

[MFGF]

Thanks MF!

I'll do that, if I can work out the admin bit (we go via a 3rd party for support).

Poor / erratic response times in Cognos is a big issue for us. While I appreciate that there can be many other factors at play here, do you, or does anybody else, have an idea of the actual impact of minimised SQL on DB response time? I'm willing to follow just about any avenue to improve response times. But if minimised SQL only has a negligible impact, maybe I won't spend too much time on it.

Thx, Michael

Hi,

It all depends on the numbers of items in your tables. If they are very wide and you are dragging back every column each time you formulate a query, it could be quite expensive. Or not. Have you looked at the non-minimised queries to see what is being retrieved vs what you'd expect?

Just as a quick sanity check, have you been into the properties of the query subjects in your linked segments and made sure that on the Test tab > SQL Settings tab, the Generate SQL setting is defined as Minimized rather than As View?

MF.

[Michael75]

Hi MF,

The tables I'm currently working with in the EFFECTIF segment are not very wide, maybe 30 columns on average. I'm getting the expected columns retrieved in all cases. As regards the settings, every QS I checked has Generate as Minimized.

Out of curiosity I tried a QS with 193 columns from a different linked segment, IDIT_SMA. And contrary to what I expected, this one generates minimised SQL. So then I started thinking my issue must be segment-related, and tried a second QS from this segment. This time, no mimimised SQL :( In case anyone is wondering, select * from is used in both, and Generate as Minimized is checked.

So now, whereas all QS in the main segment behave as expected, I have no pattern for those in linked segments. I'm now seeing about raising a PMR for this.

[MFGF]

I'm now seeing about raising a PMR for this.

A wise choice, sir! :)

MF.