Professional Web Applications Themes

Query Optimizer Problem with Views in where Clause - Microsoft SQL / MS SQL Server

Hi! I am using Sql 7 SP3. I have noticed strange Query plan when using views. Here is the case: Table1 has Col1, Col2, Col3 .... etc. Col1 is Varchar column and has Clustered Index. When I created view V1 As select * from Table1 where Col1 like 'N%' and Then ran the statement Select * from V1 I saw it is using Clustered Index SEEK on Col1 and hence result set is faster. But When I created view V2 As select * from Table1 and Then ran the statement Select * from V2 where Col1 like 'N%' I saw ...

  1. #1

    Default Query Optimizer Problem with Views in where Clause

    Hi! I am using Sql 7 SP3. I have noticed strange Query plan when using
    views. Here is the case:

    Table1 has Col1, Col2, Col3 .... etc.

    Col1 is Varchar column and has Clustered Index.

    When I created view

    V1 As select * from Table1 where Col1 like 'N%'

    and Then ran the statement

    Select * from V1

    I saw it is using Clustered Index SEEK on Col1 and hence result set is
    faster.

    But

    When I created view

    V2 As select * from Table1

    and Then ran the statement

    Select * from V2 where Col1 like 'N%'

    I saw it is using Clustered Index SCAN on Col1 and Filtering on 'N%', hence
    taking longer to get result set back.

    My question is, Why Query Optimizer chose two seperate plan for above case.
    As far as I know, for Optimizer, view should be no different than direct sql
    statement. Anyone has seen this problem in Sql 7?

    Thanks in advance.



    james Guest

  2. #2

    Default Re: Query Optimizer Problem with Views in where Clause

    If you're on SP3, I suggest to update to the latest service pack. I am
    unable to reproduce it here.

    create table t(col1 varchar(30) primary key,col2 int)
    go
    create view v1
    as
    select * from t where col1 like 'N%'
    go
    create view v2
    as
    select * from t
    go
    set showplan_text on
    go
    select * from v1
    select * from v2 where col1 like 'N%'
    go
    set showplan_text off
    go

    drop view v2,v1
    drop table t
    go


    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]



    "james" <kushbrandes.com> wrote in message
    news:usSpp22RDHA.1920TK2MSFTNGP11.phx.gbl...
    > Hi! I am using Sql 7 SP3. I have noticed strange Query plan when using
    > views. Here is the case:
    >
    > Table1 has Col1, Col2, Col3 .... etc.
    >
    > Col1 is Varchar column and has Clustered Index.
    >
    > When I created view
    >
    > V1 As select * from Table1 where Col1 like 'N%'
    >
    > and Then ran the statement
    >
    > Select * from V1
    >
    > I saw it is using Clustered Index SEEK on Col1 and hence result set is
    > faster.
    >
    > But
    >
    > When I created view
    >
    > V2 As select * from Table1
    >
    > and Then ran the statement
    >
    > Select * from V2 where Col1 like 'N%'
    >
    > I saw it is using Clustered Index SCAN on Col1 and Filtering on 'N%',
    hence
    > taking longer to get result set back.
    >
    > My question is, Why Query Optimizer chose two seperate plan for above
    case.
    > As far as I know, for Optimizer, view should be no different than direct
    sql
    > statement. Anyone has seen this problem in Sql 7?
    >
    > Thanks in advance.
    >
    >
    >

    oj Guest

  3. #3

    Default Re: Query Optimizer Problem with Views in where Clause

    Thanks for the reply. Actually In order to simplify the question I didn't
    mentioned that the view joins two other table but the execution plan around
    join(Hash) area seems to be fine and same on both cases. The only difference
    between them is, when I put where clause inside view, optimizer is using
    INDEX SEEK but when I put where clause outside of view optimizer is using
    INDEX SCAN and FILTER. Since this is the first thing optimier does, I only
    posted question regarding that part.

    "oj" <nospam_ojngohome.com> wrote in message
    news:uXN5Jh3RDHA.940TK2MSFTNGP11.phx.gbl...
    > If you're on SP3, I suggest to update to the latest service pack. I am
    > unable to reproduce it here.
    >
    > create table t(col1 varchar(30) primary key,col2 int)
    > go
    > create view v1
    > as
    > select * from t where col1 like 'N%'
    > go
    > create view v2
    > as
    > select * from t
    > go
    > set showplan_text on
    > go
    > select * from v1
    > select * from v2 where col1 like 'N%'
    > go
    > set showplan_text off
    > go
    >
    > drop view v2,v1
    > drop table t
    > go
    >
    >
    > --
    > -oj
    > RAC v2.2 & QALite!
    > [url]http://www.rac4sql.net[/url]
    >
    >
    >
    > "james" <kushbrandes.com> wrote in message
    > news:usSpp22RDHA.1920TK2MSFTNGP11.phx.gbl...
    > > Hi! I am using Sql 7 SP3. I have noticed strange Query plan when using
    > > views. Here is the case:
    > >
    > > Table1 has Col1, Col2, Col3 .... etc.
    > >
    > > Col1 is Varchar column and has Clustered Index.
    > >
    > > When I created view
    > >
    > > V1 As select * from Table1 where Col1 like 'N%'
    > >
    > > and Then ran the statement
    > >
    > > Select * from V1
    > >
    > > I saw it is using Clustered Index SEEK on Col1 and hence result set is
    > > faster.
    > >
    > > But
    > >
    > > When I created view
    > >
    > > V2 As select * from Table1
    > >
    > > and Then ran the statement
    > >
    > > Select * from V2 where Col1 like 'N%'
    > >
    > > I saw it is using Clustered Index SCAN on Col1 and Filtering on 'N%',
    > hence
    > > taking longer to get result set back.
    > >
    > > My question is, Why Query Optimizer chose two seperate plan for above
    > case.
    > > As far as I know, for Optimizer, view should be no different than direct
    > sql
    > > statement. Anyone has seen this problem in Sql 7?
    > >
    > > Thanks in advance.
    > >
    > >
    > >
    >
    >

    james Guest

Similar Threads

  1. CFGRID QUERY USING A WHERE CLAUSE
    By bflophil in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: February 17th, 05:46 PM
  2. Redbrick Query Optimizer behaving funny.
    By Asheesh Rastogi in forum Informix
    Replies: 0
    Last Post: October 21st, 03:16 PM
  3. Optimizer Problem in Where clause in View
    By james in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 11th, 03:42 PM
  4. Replies: 4
    Last Post: July 8th, 07:00 AM
  5. Query optimizer in Oracle 7.3.4
    By ronia in forum Oracle Server
    Replies: 1
    Last Post: January 8th, 05:08 AM

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