Filter on indexes - but be careful!
If you are familiar with databases, you will know that your first preference will be to filter on the clustered index, then on any non-clustered indexes. Any other filtering will force a table scan - a potentially costly operation. But did you know that there are rules for filtering on indexed fields?
For example, assuming order date was indexed, this:
is less efficient than:
even though they mean the same thing. The difference is that the first performs an index scan (it looks at every row, performs the calculation, then determines if the row should be included), while the second performs a seek (it positions directly on the first row where the order date is greater than or equal to 30 days ago). The second option has to read far fewer records so it is more efficient.
A general rule of thumb is to know your indexed fields, and, if possible, do not perform calculations on those fields. Apply the calculation on anything the field is being compared to. Keep in mind that it is still more efficient to perform an index scan rather than a table scan since a table has many more fields to read through than an index.
There are many ways to influence performance - from the database to tweaking settings in the Cognos application and beyond. We have shown a few tips to get you started on the path to performance improvement (isn't that what BI is really about?), and I am sure that there will be plenty more tips to show in future articles.
For example, assuming order date was indexed, this:
(current_date,[Order date]) < 30is less efficient than:
[Order date] > (current_date - 30)even though they mean the same thing. The difference is that the first performs an index scan (it looks at every row, performs the calculation, then determines if the row should be included), while the second performs a seek (it positions directly on the first row where the order date is greater than or equal to 30 days ago). The second option has to read far fewer records so it is more efficient.
A general rule of thumb is to know your indexed fields, and, if possible, do not perform calculations on those fields. Apply the calculation on anything the field is being compared to. Keep in mind that it is still more efficient to perform an index scan rather than a table scan since a table has many more fields to read through than an index.
There are many ways to influence performance - from the database to tweaking settings in the Cognos application and beyond. We have shown a few tips to get you started on the path to performance improvement (isn't that what BI is really about?), and I am sure that there will be plenty more tips to show in future articles.
Comments
Post a Comment