1

I wanted to make a rdlc report that gets the result from 3-4 tables using joins based on the inputdate criterions. I would like to know which is better as backend, a view or stored procedure to get the results faster and for performnace gain as im expecting 10000-30000 records as result. My database is SQL Server 2008. Please help!

Thanks

Wilma
  • 51
  • 1
  • 1
  • 4
  • possible duplicate of [SQL-Server Performance: What is faster, a stored procedure or a view?](http://stackoverflow.com/questions/1603853/sql-server-performance-what-is-faster-a-stored-procedure-or-a-view) – gbn Jul 31 '11 at 11:39

3 Answers3

1

Without more details, there's going to be little or no difference.

If the query can be expressed as a simple view, then that's the way you should do it.

What's more likely to impact the speed of the query is the indexes you have in place.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Hi,thanks for your reply. If i use a view then my filtering criterion wil be specified at the report level. That means im collecting the entire data and dropping the rest based on filter. I was thinking if i use stored procedure then i will pulling only the filtered result set to the report so no dropping occurs. Please correct me if my understanding is wrong. – Wilma Jul 31 '11 at 11:47
  • only the data that meets the criteria is sent 'across the wire' – Mitch Wheat Jul 31 '11 at 11:48
  • My doubt is if i use a view then my filtering criterion will be specified at the report level. That means im collecting the entire data and dropping the rest based on filter. But if i use stored procedure i take the filtered result directly. So in that case SP will be faster. Correct? – Wilma Jul 31 '11 at 12:05
1

I'm not sure why people think their choices are a view or a stored procedure.

Views serve many purposes. If you are using the same base query for different reports, or want to simplify schema, implement some type of security, etc., then a view may make sense. Views are NOT however implemented for performance reasons (let's leave indexed views out of this for a second). A standard view is never going to speed up a query, it just makes it more convenient to reference the view rather than repeat all of the joins etc. that make up the view.

Your report should be calling a stored procedure - this modularizes the query code, and provides a central place to modify the code. Whether that stored procedure references a view or not is a different question.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

A view adds one more layer of abstraction over a query and is not pre-optimized. So if you were to just run the underlying query you would get better performance than with a view.

Duncan Gravill
  • 4,552
  • 7
  • 34
  • 51
  • Just a nit-pick - I would say you would get better or equal performance compared to a view. Just putting a view in between isn't necessarily going to make performance worse, at least not noticeably. – Aaron Bertrand Jul 31 '11 at 16:22