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