View and SQL2000 performance

Question

View and SQL2000 performance

David Lester

Hall of Fame

Points: 3647
More actions
February 11, 2009 at 3:18 pm

#127674

I am being told by the hardware manager and IT director that having a large number of views saved in a SQL2000 database on a server impacts performance.
I find this tough to believe, as views are simply saved TSQL statements, which are returned from a sys table. So I am asking, is there any reason to think there would be a performance hit on a server which has 100 views, or 1000 views, or 5000 views?
The performance hits are not small, we are talking a 6 min process jumping to 120 min. As a point of note we are talking on average 5-10 concurrent users accessing the server. The total data volume of the database is 100 gb.
The IT department repeatedly tells us the problem is number of views, and fragmentation (which they have done 4 times now without a performance gain).
Am I way off base on thinking number of views, objects in a DB has little or no influence on the performance?
:Wow:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

The simple existence of a view (or even 1,000's of them) has absolutely no impact on the performance of your server. A view will only affect performance if someone is actually running queries that use the view.

There are a number of possible reasons for a view to take a long time and NONE of them have anything to do with the fact that the query is encapsulated in a view (although if you are using a view that uses other views, this may be part of the problem)

I would check the following first:

- are there appropriate indexes in place

- is the level of fragmentation under control

- is the query being blocked my another process

- are statistics for the tables involved update to date

Lowell SSC Guru Points: 323518 More actions · Answer 2

and building on happycat's list:

how are applications getting data from the views? if the application is doing SELECT * from 100GigView with no WHERE statement, and then filtering the recordset client side, the code from the app needs to be reviewed.

other things, like sticking more than a few hundred values in a drop down menu, makes the Client application seem incredibly slow, when the server has already passed data to the client. this is something we found out the hard way.

have you started a profile to see which queries are taking a long time, or if the slowness is on the client when trying to render?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Lester Hall of Fame Points: 3647 More actions · Answer 3

Thank you for the replies.

I actually do know, myself, what the issue really is, and have all sorts of books, articles, traces, stats to back it up. All of which the IT director and hardware manager refuse to glance at, and continue harping on number of views, and fragmentation (which they have done 4 OS defrags in the last month, and I have the DBBC defrag scheduled for each week).

I just needed a quick reality check.

Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 4

When it comes to beating a dead horse with management, I've been there as well....sometimes they don't want to listen.

What is the real issue by the way? cursors? we might be able to back you up even more documentation wise, but now I'm curious as to what the real issue is.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Lester Hall of Fame Points: 3647 More actions · Answer 5

Thanks.

The real issue is disk I/O bottlenecks.

Among issues...

Raid5

tempdb, translogs, and dbs on the same disks.

antivirus software running in real time, constant run that is.

There are more, but are mostly obscured by these big ones.