2

We started using COGNOS about 3 years ago. We have used COGNOS 8 and are now on COGNOS 10. We are constantly being told that using dynamic SQL queries instead of using the COGNOS model is extremely bad in that it causes performance issues and that it is not recommended by IBM. We have never had a problem that was specific to dynamic SQL and they perform just as good as reports that use the model.

Are there any performance issues or drawbacks that are specific to dynamic SQL queries? Is it really recommended by IBM that they not be used?

I understand that the model is great for at-hoc reporting and for users who do not know SQL. But for developers, the dynamic SQL seems to be a better option especially if they do not have any control over the COGNOS model. (We have to request and document needed changes the model)

Appreciate your comments/feedback.

Mark
  • 115
  • 2
  • 13
  • 3
    Lots of negatives to me. You can't enforce any consistency. What's to stop me from joining the same tables together in a completely different manner from you? Maintenance is a nightmare. If something changes in your database, you have to change it in every single report, instead of changing it once in the model. I believe it will also prevent you from taking advantage of some of the newer functionality in Cognos (DQM, dynamic cubes). – Andrew Nov 14 '14 at 20:04
  • 1
    @Andrew - Thank you for the comment. I completely agree with you on the maintenance issues but I am really just looking at this from a performance standpoint and are there any performance drawbacks in using dynamic SQL. – Mark Nov 17 '14 at 14:02
  • 1
    If there are performance issues these need to be addressed in the model. Cognos determines how to generate its SQL based on the model you create, so if something is off there you will likely want to invest some time in identifying and fixing issues in the model. Doing what you suggest can actually harm performance further because Cognos will then issue metadata calls to retrieve details of every column you're querying on every execution and as Andrew said, some functionality will fail. – chsh Nov 17 '14 at 15:02

2 Answers2

2

Manually building your queries with Dynamic SQL may we worse for many reasons (extensability, maintainability, reusability), but performance wise it is only limited by your own SQL query writing abilities. This means in some cases it will be faster than using the Cognos model. There are no speed disadvantages to using dynamic SQL.

That being said, you are missing alot of the benefits of Cognos if you are not leveraging the model. Your ability to maintain consistency, make broad changes without rewriting reports, and quickly produce new reports will be severely diminished with Dynamic SQL.

If your environment is small, dynamic sql may meet your needs. Especially for odd one-off reports that use tables and relationships that have little to do with your other reports. Or if there is a specific way you want to force indexes to be used, this may be achieved with dynamic sql.

Edit: It is important to note that criteria established in Report Studio Filters will not be passed into your Dynamic SQL queries until after the data has been retrieved. For large data sets this can be extremely inefficient. In order to pass criteria into your Dynamic SQL from your prompts, use #prompt('yourPromptVariableNamehere')# or #promptmany('yourMultiSelectPromptVariablehere')#. A rule of thumb is this, run your Dynamic SQL query outside of cognos and see how much data is being returned. If you have a giant sales query that at a minimum needs to be filtered on date or branch, put a Prompt in the prompt page to force the user to select a specific date/period/date range/branch/etc. into your prompts, and add the criteria into your Dynamic SQL Statement with the prompt/promptmany syntax. Prompts can still be used as regular filters inside your Report Studio queries, but all of that criteria is filtered AFTER the result set is returned from the database if you are using Dynamic Queries without prompt/promptmany.

Damienknight
  • 1,768
  • 2
  • 16
  • 30
1

When it comes to performance, when you introduce dynamic SQL, it wont be able to use the caching abilities that Cognos offers (system wise).

On the other hand, its obvious that you can tune the SQL better than the machine. I wouldn't say dynamic SQL can cause performance issues in general.

IBM doesn't recommend dynamic SQL because only with a proper model, build with framework manager, you can use all the features of Cognos.

toper
  • 66
  • 3