Professional Web Applications Themes

optimize for n rows - IBM DB2

hi, i try to use sql optimize for n rows to speed the performance , so i try to use db2 explain to get the access plan to compare with and without optimize for n rows , i found that sometimes it is same , sometimes with optimize for n rows is slower, why is that? will this actually help?...

  1. #1

    Default optimize for n rows

    hi, i try to use sql optimize for n rows to speed the performance ,
    so i try to use db2 explain to get the access plan to compare with and
    without optimize for n rows , i found that sometimes it is same ,
    sometimes with optimize for n rows is slower, why is that? will this
    actually help?
    xixi Guest

  2. #2

    Default Re: optimize for n rows

    "xixi" <dai_xi> wrote in message
    news:c0f33a17.0307030737.75891dfbposting.google.c om...
    > hi, i try to use sql optimize for n rows to speed the performance ,
    > so i try to use db2 explain to get the access plan to compare with and
    > without optimize for n rows , i found that sometimes it is same ,
    > sometimes with optimize for n rows is slower, why is that? will this
    > actually help?
    Optimize for n rows will sometimes turn off sequential prefetch if the n is
    small enough. This can help performance if DB2 starts pre-fetching rows that
    are never fetched in the program.

    If you specify a value for n that is accurate (the actual number of rows
    fetched) it shouldn't hurt performance, but there is not guarantee that it
    will help.

    When you say it is slower with optimize for n rows, do you mean in the
    explain, or in a real world elapsed time test. The DB2 explain will not know
    the actual number of rows fetched so the timerons could be misleading.


    Mark A Guest

  3. #3

    Default Re: optimize for n rows

    By using this clause, you modify the row blocking factor.
    I think it is/was MIN( fetch_first, optimize_for).


    read this section of the doc
    Guidelines for restricting select statements

    PM


    PM \(pm3iinc-nospam\) Guest

Similar Threads

  1. Replies: 7
    Last Post: September 2nd, 05:44 AM
  2. PDF optimize
    By Ashish in forum Brainstorming Area
    Replies: 0
    Last Post: June 30th, 09:12 AM
  3. PDF won't optimize
    By Beth_R_Phillips@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 3
    Last Post: February 8th, 11:25 PM
  4. How to optimize?
    By Krillo webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 1
    Last Post: August 4th, 06:38 PM
  5. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 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