sql server reporting services: I just wondered What's the disadvantegs of using direct queries for my
datasets in comparison with sotred procedures?

Thanks
Ray

A couple advantages are stored procedures have stored/cached execution
plans and are optimized by the query engine, resulting in better
performance for reports with large or complex datasets. I also like
the fact that you can comment sprocs.

I think of direct query as digressing to a two-tiered architecture; you have
all this embedded SQL in all these reports... so if a schema change has to
be made (rename an attribute, snowflake a dimension, etc...) then you just
don't know what reports it is going to break. Also you have to open up a lot
of your database for query especially if you want to take advantage of
caching and snapshots since they have to use stored credentials and you are
not securing data per user like you might with Windows security (but Windows
security is not ideal in many situations). Finally, things can get a little
tricky with dynamic query and situational result-sets if you have to handle
it all in a direct query.

But if you are using stored procedures you essentially have a middle tier to
your reporting applications, where someone that is not a Reporting Services
specialist can contribute (write stored procs) or troubleshoot, and schema
changes can be made while the stored procs are updated to still pass through
the same dataset with the same column name. Also you can only grant execute
rights to report stored procedures for the credentials you are storing for
cached and snapshot reports. And finally, you can just handle a lot more in
a stored proc, I like having the flexibility to load everything into a table
variable or similar.

If you do use stored procs, I recommend standardized naming of all report
procs so they sort together separate from other procs ("csp_rpt_xxxxx").
--
Douglas McDowell douglas@nospam.solidqualitylearning.com


[quoted text, click to view]

DBA's will all tell you that everything should be a stored procedure. My
personal feeling is that this is overkill. Not every shop needs to squeeze
every last ounce of performance and having the SQL in the report is just
fine. What I tend to do is create a stored procedure if it is multiple
steps. If it is a single query I just have it in the report. I know right
where it is. I personally think that ending up with 100's or thousands (in a
bigger shop) of stored procedure is just plain unfriendly environment. If
you need to make major schema changes then create a view that the reports
will still go off of and change the schema.

It isn't like adding a field or changing the field's size is going to break
a report.

By the way, the SQL Server MVP's are incredibly good DBAs but most of them
are from very intense shops. It is not small or midsized businesses where
speed of development and making development easier is important. There is a
reason that MS ships a GUI query tool. It is because plenty of people create
the query and have it in the report. Everything is not turned into a stored
procedure.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Stored procs are generally a bit faster as they cache their plan better than
direct access, also stored procs give you better security as you don't have
to allow access to the underlying tables, just exec permission on the proc,
thus users connecting through Access or Excel can't fiddle with the data so
easily. If you are managing the connection through a separate account this
is not such an issue.

--

Mary Bray [SQL Server MVP]
Please reply only to newsgroups

[quoted text, click to view]