[dba-SQLServer] Views

ok...

now i am a bit confused.  One way i use views is to create summary data tables (using grouped data)  This normally compacts all the transactional level data into a tighter grouping (say department level for example).  Then I use an ID to just pull in the data I need to the Access FE (usually in a form) for that specific grouping.

SELECT * FROM vMySummaryView WHERE DeptID = @DeptID

Are you saying that this will still pull ALL of the data into the Access FE?  surely not?

Although it sounds like creating the output of the view via a sproc will run much faster - I thought that the view (being already made) would be quick, but this "not compiled or optimized in the same way as stored procedures" bit has me thinking.

The database I used will have 100,000's of rows of data rather than millions.  And probably 40 or so users hitting it at any one time - not massive by any means.

I read about "partitioned views" as well - again they are meant to be fast, but I have never tried it.

Anyone else want to add more to this?

cheers
Darryl.



-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Arthur
Fuller
Sent: Wednesday, 8 October 2008 2:09 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Views


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.
>
>
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses.  No warranty is made that this material is free from
computer virus or any other defect or error.  Any loss/damage incurred by using this
material is not the sender's responsibility.  The sender's entire liability will be
limited to resupplying the material.