Professional Web Applications Themes

Views or WHERE - Microsoft SQL / MS SQL Server

Hi group, Generally speaking, is there any sort of rule of thumb as to when it would be better (faster, more efficient) to start using views instead of using WHERE clauses all the time? For example: Table: Users UserID int, identity '''other columns like username, password, etc. Table: Links LinkID int, identity UserID FK Link varchar(2083) So, let's say that there are 50 users in this system, and each user has about 350 links in the links table. Each time I'd want to retrieve a list of links for a specific user, I'd do "select link from links where userid=1" ...

  1. #1

    Default Views or WHERE

    Hi group,

    Generally speaking, is there any sort of rule of thumb as to when it would
    be better (faster, more efficient) to start using views instead of using
    WHERE clauses all the time? For example:

    Table: Users
    UserID int, identity
    '''other columns like username, password, etc.

    Table: Links
    LinkID int, identity
    UserID FK
    Link varchar(2083)

    So, let's say that there are 50 users in this system, and each user has
    about 350 links in the links table. Each time I'd want to retrieve a list
    of links for a specific user, I'd do "select link from links where userid=1"
    for example. Instead of that, I'm thinking of creating a view for each user
    that contains only his links, and I could then just do "select link from
    view1" for example.

    I would be actually using other WHERE conditions in my selects though, like
    "where categoryID=3 and dateadded>'2/1/2003', or other such things. So, it
    almost seems to me that by using views, I'd essentially be doing two
    queries - one that generates the view, and then one that I execute. But, it
    only seems that way to me because I do not understand the mechanics behind
    views, like if these are stored in memory or virtual tables, or if they are
    "refreshed" each time they are called upon.

    Thank you for any insight.

    Ray at home


    Ray Guest

  2. #2

    Default Re: Views or WHERE

    Rather than having views based on the number of users, a better solution
    would be to encapsulate the retrieval logic inside a stored procedure and
    just call the procedure with appropriate number of arguments.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Ray at <%=sLocation%>" <sefrhja7yasdf.com> wrote in
    message news:ODmg9$phx.gbl... 
    userid=1" 
    user 
    like 
    it 
    it 
    are 


    SriSamp Guest

  3. #3

    Default Views or WHERE

    Hi Ray.

    As you've already indicated, the problem you'll face with
    using views for this is that you can't pass a parameter to
    a view, so the WHERE clause would need to be hard-wired to
    a specific UserID.

    A table valued, user defined function might be better for
    the task, as it can take a parameter & work within a
    select (as a view can).

    eg:

    use tempdb
    go
    set nocount on
    go
    create table users (
    userid int primary key
    , username varchar(200)
    )
    go
    create table links (
    linkid int primary key
    , userid int foreign key references users (userid)
    , linkurl varchar(7000)
    )
    go
    create table addressinfo (
    userid int
    , addresstype char(2) not null check (addresstype in
    ('hm', 'wk'))
    , address varchar(7000)
    , constraint pk_addressinfo primary key (userid,
    addresstype)
    )
    go
    insert into users (userid, username) values (1, 'bill')
    insert into users (userid, username) values (2, 'bob')
    go
    insert into links (linkid, userid, linkurl) values (1,
    1, 'http://www.microsoft.com')
    insert into links (linkid, userid, linkurl) values (2,
    2, 'http://www.ibm.com')
    insert into links (linkid, userid, linkurl) values (3,
    2, 'http://www.oracle.com')
    go
    insert into addressinfo (userid, addresstype, address)
    values (1, 'hm', '1 Smith St, Toorak')
    insert into addressinfo (userid, addresstype, address)
    values (1, 'wk', '50 Swanston St, Melbourne')
    insert into addressinfo (userid, addresstype, address)
    values (2, 'hm', '20 Davis cr, Richmond')
    insert into addressinfo (userid, addresstype, address)
    values (2, 'wk', '265 Exhibition St, Melbourne')
    go
    create function user_links(userid integer)
    returns user_links table (
    userid int
    , username varchar(200)
    , linkid int
    , linkurl varchar(7000)
    )
    as
    begin
    insert into user_links (
    userid
    , username
    , linkid
    , linkurl
    )
    select u.userid
    , u.username
    , l.linkid
    , l.linkurl
    from users as u
    inner join links as l on u.userid = l.userid
    where u.userid = userid

    return

    end
    go
    /* then, you could issue standard selects against the
    function, eg:*/
    select *
    from user_links(2) as ul
    go
    /* even better - you can join other tables / views onto
    the
    function's resultset output, eg: */
    select *
    from user_links(2) as ul
    join addressinfo as ai on ul.userid = ai.userid
    go
    drop function user_links
    go
    drop table addressinfo
    go
    drop table links
    go
    drop table users
    go

    HTH

    Regards,
    Greg Linwood
    SQL Server MVP

     
    to when it would 
    instead of using 
    each user has 
    to retrieve a list 
    links where userid=1" 
    view for each user 
    do "select link from 
    selects though, like 
    such things. So, it 
    be doing two 
    I execute. But, it 
    mechanics behind 
    tables, or if they are 
    Greg Guest

  4. #4

    Default Views or WHERE

    Views have definition which is the select statement.

    The result returned by views are temporarily cached like
    the resultset of any other select statement.

    Every time you call the view execution plan is made and
    resultset is created.

    They are not stored in a virtual table.

    Purpose of view is:

    security

    viewing of distributed data

    really complex queries



     
    to when it would 
    instead of using 
    each user has 
    to retrieve a list 
    links where userid=1" 
    view for each user 
    do "select link from 
    selects though, like 
    such things. So, it 
    be doing two 
    I execute. But, it 
    mechanics behind 
    tables, or if they are 
    Arun[Symbiosis] Guest

  5. #5

    Default Re: Views or WHERE

    Thank you both very much.

    Ray at home

    "Ray at <%=sLocation%>" <sefrhja7yasdf.com> wrote in
    message news:ODmg9$phx.gbl... 
    <trimmed>


    Ray Guest

  6. #6

    Default Re: Views or WHERE

    Arun,

    For the record, row-level security implemented through views
    or table-valued functions is not always secure, although the
    ....where suser_sid = user_sid_column
    is widely used.

    Whether privileged information can be revealed depends
    on quite a few things, but if no breach can be risked, something
    should be added to force the view or function result set to be
    materialized, such as

    select top 2000000000 * from
    ....
    order by <whatever is likely to be fastest>

    Steve Kass
    Drew University


    "Arun[Symbiosis]" <com> wrote in message
    news:071f01c34ce0$888af820$gbl... 
    > to when it would 
    > instead of using 
    > each user has 
    > to retrieve a list 
    > links where userid=1" 
    > view for each user 
    > do "select link from 
    > selects though, like 
    > such things. So, it 
    > be doing two 
    > I execute. But, it 
    > mechanics behind 
    > tables, or if they are [/ref]


    Steve Guest

  7. #7

    Default Re: Views or WHERE

    Ray,

    IMHO

    assume in your eg that you need info from multiple tables i would create 1
    view that will hold all the result rows from the tables you want to join

    e.g.
    create view UserLinkInfo as
    a.userid ,b.link, c.OtherInfoField
    from users a inner join links b on
    a.userid = b.userid
    inner join OtherInfo c on
    b.linkid = c.OtherInfoFK
    where SomeCriteria = SomeOtherCriteria

    The more joins and criteria the more complex it becomes (that is why we keep
    it in a view) so when you want this info for a particular user you just
    select * from UserLinkInfo where userid = 1

    if categoryID=3 and dateadded>'2/1/2003' is going to be constant put it in
    the view if not put it in the app

    so the simple answer is if you have large amounts of common code keep that
    in the view and filter with where clauses when you select from the view

    Regards

    Reg




    "Ray at <%=sLocation%>" <sefrhja7yasdf.com> wrote in
    message news:ODmg9$phx.gbl... 
    userid=1" 
    user 
    like 
    it 
    it 
    are 


    Reg Guest

Similar Threads

  1. views in 8.0
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 11th, 01:26 PM
  2. 2 views
    By duff@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 4
    Last Post: May 28th, 06:03 PM
  3. Views
    By Guy Brown in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 08:47 PM
  4. Replies: 4
    Last Post: July 8th, 07:00 AM
  5. Parameterized Views
    By Nice Chap in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 4th, 01:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139