10

I infrequently (monthly/quarterly) generate hundreds of Crystal Reports reports using Microsoft SQL Server 2005 database views. Are those views wasting CPU cycles and RAM during all the time that I am not reading from them? Should I instead use stored procedures, temporary tables, or short-lived normal tables since I rarely read from my views?

I'm not a DBA so I don't know what's going on behind the scenes inside the database server.

Is it possible to have too many database views? What's considered best practice?

ConcernedOfTunbridgeWells
  • 64,444
  • 15
  • 143
  • 197
Zack Peterson
  • 56,055
  • 78
  • 209
  • 280

4 Answers4

9

For the most part, it doesn't matter. Yes, SQL Server will have more choices when it parses SELECT * FROM table (it'll have to look in the system catalogs for 'table') but it's highly optimized for that, and provided you have sufficient RAM (most servers nowadays do), you won't notice a difference between 0 and 1,000 views.

However, from a people-perspective, trying to manage and figure out what "hundreds" of views are doing is probably impossible, so you likely have a lot of duplicated code in there. What happens if some business rules change that are embedded in these redundant views?

The main point of views is to encapsulate business logic into a pseudo table (so you may have a person table, but then a view called "active_persons" which does some magic). Creating a view for each report is kind of silly unless each report is so isolated and unique that there is no ability to re-use.

Matt Rogish
  • 24,435
  • 11
  • 76
  • 92
2

A view is a query that you run often with preset parameters. If you know you will be looking at the same data all the time you can create a view for ease of use and for data binding.

That being said, when you select from a view the view defining query is run along with the query you are running.

For example, if vwCustomersWhoHavePaid is:

Select * from customers where paid = 1

and the query you are running returns the customers who have paid after August first is formatted like this:

Select * from vwCustomersWhoHavePaid where datepaid > '08/01/08'

The query you are actually running is:

Select * from (Select * from customers where paid = 1) where datepaid > '08/01/08'

This is something you should keep in mind when creating views, they are a way of storing data that you look at often. It's just a way of organizing data so it's easier to access.

Benny Hill
  • 6,191
  • 4
  • 39
  • 59
Sara Chipps
  • 9,322
  • 11
  • 58
  • 103
1

You ask: What's going on behind the scenes?

A view is a bunch of SQL text. When a query uses a view, SQL Server places that SQL text into the query. This happens BEFORE optimization. The result is the optimizer can consider the combined code instead of two separate pieces of code for the best execution plan.

You should look at the execution plans of your queries! There is so much to learn there.

SQL Server also has a concept of a clustered view. A clustered view is a system maintained result set (each insert/update/delete on the underlying tables can cause insert/update/deletes on the clustered view's data). It is a common mistake to think that views operate in the way that clustered views operate.

Tom H
  • 46,766
  • 14
  • 87
  • 128
Amy B
  • 108,202
  • 21
  • 135
  • 185
1

The views are only going to take up cpu/memory resources when they are called.

Anyhow, best practice would be to consolidate what can be consolidated, remove what can be removed, and if it's literally only used by your reports, choose a consistent naming standard for the views so they can easily be grouped together when looking for a particular view.

Also, unless you really need transactional isolation, consider using the NOLOCK table hint in your queries.

-- Kevin Fairchild

Kevin Fairchild
  • 10,891
  • 6
  • 33
  • 52
  • I agree with the answer, but not with the NOLOCK suggestion. I see plenty of people just putting NOLOCK everywhere and then wondering why they are having consistency issues. Such clause has its uses, but it's abused most of the time, I would not recommend to use it "just so". Using READ_COMMITTED_SNAPSHOT would a much better option to avoid locking. – Diego Aug 29 '12 at 11:39
  • @Diego, *anything* can be abused ;) But for simple queries, especially once you don't absolutely care about dirty reads, it's really not as bad as people make it out to be. Heck, StackOverflow even uses it on plenty of its queries. See also: http://www.codinghorror.com/blog/2008/08/deadlocked.html and http://stackoverflow.com/a/1453087/3743 At the end of the day, though, it's a judgement call... and needs to be based on your data and what potential trade-offs you're willing to make. – Kevin Fairchild Aug 29 '12 at 14:17
  • That's true, it's just that way too many people see the NOLOCK as the holy grail that makes work the poor queries they wrote. Many developers don't know, don't like or are simply cr.p at SQL and they try to steer clear from it; NOLOCK seems an easy escape route compared to code review and heavy refactoring. I had to maintain a system completely built on NOLOCKS, removing a single one would cause it to crash. It was so bad that I heavily argued with the developers on a daily basis, so much they were "NOLOCK worshippers". I say "in case of doubt, don't use it. If you're sure, don't use it". :) – Diego Aug 29 '12 at 14:23