Views: A view contains five columns, c1..c5

  • Ripple

    Right there with Babe

    Points: 777

    If I write a select statement against the view using only columns c1 & c2, do the other columns in the view definition, c3, c4 and c5, get read from disk since they are part of the view definition?

    Thanks in advance for any help.

    - Brendan

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

    My guess is that they do, but I will do some testing.

    Steve Jones

    steve@dkranch.net

  • Ripple

    Right there with Babe

    Points: 777

    Steve - ever find anything out about this?

    I have not had any luck.

    We improved our performance by adding RAM, but I'd still be interested if you ever figured anything out.

    Cheers.

    - Brendan

  • Andy Warren

    SSC Guru

    Points: 119694

    Just a swag, I don't see how you could help it. SQL would have to pick up the entire page in order to read the rows to get the pieces of data you want. It's page reads that hurt, not the hunting down of columns within the page. If you were to find that it was really hurting performance you could split the table vertically, that would increase your IO. That would truly be a last resort.

    Andy

    Andy

  • nigelrivett

    SSCertifiable

    Points: 5362

    You could also add a non-clustered index on cols c1 & c2. It would also have to cover the query to be useful but would mean the minimum page reads.

    The query will use whatever indexes it thinks are best (the view will be incorporated into the query plan as though it was part of the select). If the data to be retrieved is included in the index then the data pages will not be read. Otherwise the data pages will be retrieved and all the row will be read into memory.


    Cursors never.
    DTS - only when needed and never to control.

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

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