Professional Web Applications Themes

Optimizer Problem in Where clause in View - Microsoft SQL / MS SQL Server

Hi! I am using Sql server 7 and found strange behavior of Optimizer when using view. Here is the brief description of the case: Table1 has col1, col2, col3 ..... col1 has cluster index. If I created view V1 as select * from Table1 where col1 like 'x%' and run the query select * from V1 then optimizer uses clustered index SEEK on col1 and bring the result very fast. But If I created view V2 as select * from Table and run the query, select * from V2 where col1 like 'x%' then optimizer does clustered index SCAN then ...

  1. #1

    Default Optimizer Problem in Where clause in View

    Hi! I am using Sql server 7 and found strange behavior of Optimizer when
    using view. Here is the brief description of the case:
    Table1 has col1, col2, col3 .....
    col1 has cluster index.
    If I created view
    V1 as select * from Table1 where col1 like 'x%'
    and run the query
    select * from V1
    then optimizer uses clustered index SEEK on col1 and bring the result very
    fast.
    But
    If I created view
    V2 as select * from Table
    and run the query,
    select * from V2 where col1 like 'x%'
    then optimizer does clustered index SCAN then does Filter on x% hence the
    result set is about 4 times slow.

    My question is, Shouldn't Query plan be same for above two cases, where only
    difference is you put where clause outside or inside of view. Why would
    Optimizer even make the difference, In fact, optimizer shouldn't even know
    whether its view or straight sql statment. It should be taken care by the
    pr before Optimizer sees it.

    Is it a optimizer problem in Sql 7? Anyone has this issue before?
    Thanks in advance


    james Guest

  2. #2

    Default Re: Optimizer Problem in Where clause in View

    Whenever SQL-Server finds a reasonably good query plan it will not
    'waste time' to search for an even better query plan. This might be
    happening here.

    If you make Table1 a big table, you will most likely get the same query
    plans.

    Gert-Jan


    james wrote:
    >
    > Hi! I am using Sql server 7 and found strange behavior of Optimizer when
    > using view. Here is the brief description of the case:
    > Table1 has col1, col2, col3 .....
    > col1 has cluster index.
    > If I created view
    > V1 as select * from Table1 where col1 like 'x%'
    > and run the query
    > select * from V1
    > then optimizer uses clustered index SEEK on col1 and bring the result very
    > fast.
    > But
    > If I created view
    > V2 as select * from Table
    > and run the query,
    > select * from V2 where col1 like 'x%'
    > then optimizer does clustered index SCAN then does Filter on x% hence the
    > result set is about 4 times slow.
    >
    > My question is, Shouldn't Query plan be same for above two cases, where only
    > difference is you put where clause outside or inside of view. Why would
    > Optimizer even make the difference, In fact, optimizer shouldn't even know
    > whether its view or straight sql statment. It should be taken care by the
    > pr before Optimizer sees it.
    >
    > Is it a optimizer problem in Sql 7? Anyone has this issue before?
    > Thanks in advance
    Gert-Jan Strik Guest

Similar Threads

  1. Replies: 0
    Last Post: October 25th, 12:54 PM
  2. #39253 [NEW]: debug_backtrace() and Zend Optimizer problem
    By programmando at gmail dot com in forum PHP Bugs
    Replies: 0
    Last Post: October 25th, 12:50 PM
  3. Union View Optimizer Creates Temp Table
    By Brian Foster in forum Informix
    Replies: 2
    Last Post: September 26th, 10:00 PM
  4. Problem with Zend Optimizer
    By Michal Zajaczkowski in forum PHP Development
    Replies: 0
    Last Post: August 29th, 10:34 AM
  5. Query Optimizer Problem with Views in where Clause
    By james in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 11th, 03:26 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