Stored Procedure vs Manual Select

Hello,

I wanted to test how Dundas works with stored procedure and what is the logic behind Dundas when a stored procedure is used.
I have tested and simple “select * from table” in manual selection and in stored procedure.
In MSSQL, the selection and the stored procedure are executed in the same time. In Dundas, the stored procedure runs 3 or 4 times slower than the manual select. I think Dundas execute the stored procedure too many times.
In my example, I have a dashboard with 10 visualization and most of them have a PoP calculated within the metric set.

What is the difference for Dundas between the select and the stored procedure, because in SQL Server are identical?

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.

Thank you for the explanation @jeff! Yesterday I had a meeting with the support team regarding this question. We measured the time for the getdate retrival in the dashboard and it’s a huge difference: a half a second for the manual select and 12 second for the stored procedure. They also clarified me some thing about this topic. Definitely, I will go with the manual select.

1 Like