Is there speed differences between Views and Stored Procedures 3

[ksbigfoot]

I am using CR10 and SQL Server 8.
In my crystal reports, I can call either a view or stored procedure to pull back the data to my report.

Does anyone know if there is a speed difference
or a benefit of using one over the other?

 

[satinsilhouette]

Views are able to be pre-populated or scheduled so that there is no run time when Crystal calls it, otherwise, Crystal plugs into the view and the view has to run.

Stored Procs are generally used to handle more complicated, accumulated data (like summarizing a bunch of different data). Crystal can call, as you say, as stored procedure, but it must wait for the stored procedure to run through its code and deliver back up the data for Crystal to display.

I think it depends upon what you need to do. Processing in SQL 8 is a lot slower than 2000/2003.

Hope this helps.

Thanks so much!
satinsilhouette

 

[ksbigfoot]

Howdy Satinsilhouette,
Wow, great answer. I gave you a star.
I was searching on Google all day long trying to find an answer to this question and this is the best one I have read.
Thanks again about the comment about SQL 8 vs 2000/2003.
I will have to do some research into that and make recommendations to the company I am contracting at.

I think I will stick with views unless I have to perform an conditional statement in my query, then I will call a stored procedure.

Thanks again,
ksbigfoot

 

[rhinok]

Another factor is that using a Stored Procedure (or a SQL Command Object, which is conceptually the same) will hinder you from efficiently using some Crystal Reports functionality.

As an example:

You have a report design requirement to have a single parameter that allows users to select one item, multiple items or all items. You can do this with Crystal Reports parameters in such a way that the criteria is always passed to the database for processing. Stored Procedure parameters, however, do not allow you to select an array (multiple) of parameter values. In order to provide the same functionality, you'd have to:

1) Create a single string-type parameter with user-delimited values and then parse out the values within the SQL itself. This is much more difficult than it needs to be....

2) Create a Crystal Reports parameter on top of the stored procedure. The problem is that this parameter will always be processed on the client instead of on the server when the stored procedure is executed (because the stored procedure has to be called before any Crystal functionality is allowed). The result is slower processing, too much data being passed over the network and an inefficient report.

As satinsilhouette said, unless you need to manipulate the data in a more advanced manner, views are typically better.

Personally, I create as many of my reports on views as I possibly can...

~Kurt

http://www.acuityreporting.com

 

[IdoMillet]

A point of disagreement: it is NOT typical to have views pre-populated (this is called materialized views) and hence, in most cases, Views are NOT faster the Stored Procedures.
In fact, Stored Procedures are pre-parsed and hence are SLIGHTLY faster than views.

On the other hand, Views are more flixible. Besides the excellent comments above by Kurt, Views can be used by other views, and can be used within a Crystal report just like any other table. This creates opportunities for flixibility, reuse, and (when needed) complex logic.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Age old issue here, and the parameter concern is why I often suggest Views, however a Stored Procedure can prove much faster as it has a precompiled execution plan that your View does not, and it can do many things that a View cannot.

In either case, creating database objects is a much better idea than embedding the query in the report (command objects) as you promote reusability and they provide simplified maintenance. If a change occurs in the database, you open the View and tweak it and you've not impacted your reporting layer in many instances.

Each requirement should be analyzed seperately, and of course the skillset of the coders involved is huge.

When using Views or Tables, you want to make sure that whatever is placed in the report selection formula(s) gets passed to the database in the Database->Show SQL Query.

-k

 

[satinsilhouette]

As you can see ksbigfoot, both are used, I think it comes down to what are you trying to do, what do you need to handle for data. I know some CR developers only work with sp's but there is that trade off in having to call the sp and it does its magic. Whereas a view can be pre-populated or it can be a dynamic view (which also has to run when it is called).

The best answer, experiement, and keep in mind how the CR engine plays with the underlying database query engine.

Thanks so much!
satinsilhouette