Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
views
- Thread starter Rottdpogs
- Start date
From the context of network IO, it will be the same.
Ex:
SELECT * From UserTable where ID > 100
and
CREATE view ShowUser AS
SELECT * From UserTable where ID > 100
and then...
SELECT * FROM ShowUser
Both will return the same amount of data.
Now from a performance point of view the 'view' has a little more work to do. The reason is because anytime you reference "ShowUser" the engine has to go into the syscomments table to get the definition of "ShowUser", so in effect it's an extra operation behind the scenes. In addition there are also entries added to syscolumns for each column in the query.
So, if you are trying to get performance out of your system by using views, don't it won't actually help. views are more of a help when you just want small reusable definitions of data in your database.
Hope this help.
Ex:
SELECT * From UserTable where ID > 100
and
CREATE view ShowUser AS
SELECT * From UserTable where ID > 100
and then...
SELECT * FROM ShowUser
Both will return the same amount of data.
Now from a performance point of view the 'view' has a little more work to do. The reason is because anytime you reference "ShowUser" the engine has to go into the syscomments table to get the definition of "ShowUser", so in effect it's an extra operation behind the scenes. In addition there are also entries added to syscolumns for each column in the query.
So, if you are trying to get performance out of your system by using views, don't it won't actually help. views are more of a help when you just want small reusable definitions of data in your database.
Hope this help.
The problems with views that I find is if they are broadly defined, using mulitple table joins. Usually views are of most use when the pull a specific type of data from a joined tables. For instance, maybe everything for sales region X.
I just ran into a major performance problem and the culprit was a view used within a select statement. WHen the view was created, the amoutnt of data was small, and when used within a select statement, the worktable created by the view in the select statement was small. But as the underlying tables sizes increased, the construction of the view information into a work table used 90% of the execution time. We actually had to break the view apart and have sql go directly against the tables.
I hope I am making sense, since I tend to ramble...
Views do serve a great purpose, but some care sometimes needs to occur.
I just ran into a major performance problem and the culprit was a view used within a select statement. WHen the view was created, the amoutnt of data was small, and when used within a select statement, the worktable created by the view in the select statement was small. But as the underlying tables sizes increased, the construction of the view information into a work table used 90% of the execution time. We actually had to break the view apart and have sql go directly against the tables.
I hope I am making sense, since I tend to ramble...
Views do serve a great purpose, but some care sometimes needs to occur.
- Status
Similar threads
