December 22, 2010 at 8:13 am
Hi
I'm rather a newbie to ssrs and t-sql, my previous experience in business objects, crystal reports and access so was hoping someone could help.
I need to create a base query and query from the dataset that returns so I can use the answers from the sub queries in a ssrs report. I want to create a base query as this will be a faster way of getting to my data and subsequently making the report faster. However, I'm not sure of the 'rules' when it comes to sql server and want to know how to store the data from my first base query -
Should I store the qry as a view, temporary table or as a stored procedure I can call?
Many Thanks, Ella 🙂
December 22, 2010 at 8:46 am
A view might be appropriate, it would depend upon the size/complexity of the base query.
If I needed to perform multiple queries against this data set then I would want it to be a temp table and perhaps even create an index on it. This would speed up your queries and reduce the overhead.
If this is something that is done regularly, you may want to consider running a job to create some aggregate or summary data on a daily basis for your reporting.
The probability of survival is inversely proportional to the angle of arrival.
December 22, 2010 at 8:52 am
All of these approaches should work. If you can put an index on a view that is not on the underlying tables, and it would help things run faster a view would be a good tool to use. If there is no advantage to a view, the a stored procedure is a good way to go. I have even used common table expressions (sort of like a temp table or table variable) in my dataset queries for SSRS and things worked great.
I hate to give you an "it depends" answer but there are a lot of things you can consider in answering this question that are outside of an easy forum response. Generally things run faster the closer to the data they are (views and stored procedures) but if it is unlikely to be used anywhere outside the report in question I will do the work in the report dataset query to avoid cluttering the db with a lot of one use views and stored procedures.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply