David Lewis
David at sierranevada.com
Tue Oct 7 13:09:45 CDT 2008
With regard to: "With a view you get back the whole result set and then your FE has to filter
it. With a sproc or table udf, you supply parameters and get back only the
relevant rows. If you are dealing with several million rows of which only
100 are of interest, this difference is dramatic."
There is of course nothing to prevent one from
SELECT columnlist
FROM vwXYZ
WHERE parameters....
----------------------------
Message: 4
Date: Tue, 7 Oct 2008 12:08:41 -0300
From: "Arthur Fuller" <fuller.artful at gmail.com>
Subject: Re: [dba-SQLServer] Views
To: "Discussion concerning MS SQL Server"
<dba-sqlserver at databaseadvisors.com>
Message-ID:
<29f585dd0810070808l22f6a30ex6a7d00a6e9a60edd at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Views haven't been replaced. They still exist, but it is important to
understand what they are. Beneath the covers, views are simply macros with
names. (In Access lingo, they are named queries.) They are not compiled or
optimized in the same way as stored procedures or table udfs. So depending
on your perspective, they are either smart or stupid.
For example, when I was doing a lot of Access ADP development, I found views
to be an excellent approach for master-detail forms because Access would
automatically do the filtering of the detail part. But the problem with
views is that they don't accept parameters, and in large databases this is a
killer. The alternatives are stored procedures and/or table udfs, both of
which accept parameters.
With a view you get back the whole result set and then your FE has to filter
it. With a sproc or table udf, you supply parameters and get back only the
relevant rows. If you are dealing with several million rows of which only
100 are of interest, this difference is dramatic.
In my current app, I have one table that is currently at 35M rows and it
will be 50M next month, and is expected to grow at that rate. A view on such
a table (not to mention its JOINs) is preposterous. You never want to see
all the data, you only want a narrow slice. Imagine a scenario in which 20
users on a LAN want to look at some slice of this data. 35M rows get sent to
each user, then filtered to the narrow slice on the client side. Using a
sproc or table udf instead, and supplying parameters that define the slice,
only the result set gets transmitted to the user. Add more users and the
resulting time difference becomes increasingly dramatic.
hth,
Arthur
On Mon, Oct 6, 2008 at 8:21 PM, Susan Harkins <ssharkins at gmail.com> wrote:
> LOL -- how ambiguous of me. Sorry! ;)
>
> Yes, version 2008.
>
> I don't have 2008, but I haven't read or heard about views being replaced
> by
> something better, although, I can't imagine how -- they are such a nifty,
> basic tool.
>
> Susan H.
>
>
>
The contents of this e-mail message and its attachments are covered by the Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended solely for the addressee(s) hereof. If you are not the named recipient, or the employee or agent responsible for delivering the message to the intended recipient, or if this message has been addressed to you in error, you are directed not to read, disclose, reproduce, distribute, disseminate or otherwise use this transmission. If you have received this communication in error, please notify us immediately by return e-mail or by telephone, 530-893-3520, and delete and/or destroy all copies of the message immediately.