Stored procedures are a ‘black box’ when it comes to performance as we have to carry all of the data that it retrieves and to any filtering work in-memory before presenting it on a dashboard. I’ll give you an example:
You have a sales data with 10,000,000 data points [EXEC GetSalesData()]
If your goal is to filter all but 3 data points to show a pie chart, you will have to have all 10,000,000 points brought into Dundas BI because there is no way with a stored procedure request something less. Once we have the 10,000,000 points, we can then filter in-memory what you requested on the dashboard. As you would expect, this is expensive from a performance standpoint.
The best thing to do it to parameterize your stored procedure so that you can have smaller and more concise return values. Basically, let the database do as much work as it can so that we don’t have to carry the load.
Alternatively if you can use an indexed view, you will get a much better result as Dundas BI can create custom queries to the data source directly. It’s much better if we can pull the 3 data points you wanted directly rather than carrying 10,000,000. <-- Recommended Approach
Note:
Warehousing or In-Memory cubes can help in some circumstances.
This is a bit of a complex question to answer in a forum post as I don’t know all your details. It’s probably best if you contact our support team if you need a more in-depth answer.
