Professional Web Applications Themes

Question about optimizer with (big) temp tables - Microsoft SQL / MS SQL Server

If I ask query yser to give me an estimated query plan, how does it work this out since my temp table will only be populated when I fire the query? I suppose this "estimated" plan is only worth so much... Now, what happens at run time, if I create a temp table and populate it with 1 million rows? Is the optimiser "aware" of this 1 million rows or does it make its plan before populating the table? If I create one or several indexes on the temp table during the execution, what does the optimiser does with it? ...

  1. #1

    Default Question about optimizer with (big) temp tables

    If I ask query yser to give me an estimated query plan, how does
    it work this out since my temp table will only be populated when I
    fire the query?
    I suppose this "estimated" plan is only worth so much...

    Now, what happens at run time, if I create a temp table and populate
    it with 1 million rows?

    Is the optimiser "aware" of this 1 million rows or does it make its
    plan before populating the table?

    If I create one or several indexes on the temp table during the
    execution, what does the optimiser does with it?


    Thanks


    Eric
    Eric Mamet Guest

  2. #2

    Default Re: Question about optimizer with (big) temp tables

    You can't work out an estimated execution plan for a query with a temporary
    table in it. (unless of course the temp table already has been created
    outside that query).

    When you run the query the Query Optimizer will generate an execution plan
    but as soon as the CREATE TABLE statement is encountered during the
    execution it will recompile the execution plan and proceed with the new
    execution plan. If you have auto update statistics on on tempdb the
    statistics will be update a few times when you insert the 1 million rows and
    that will cause recompiles too. So the optimizer is aware of the fact that
    there are 1 million rows in the table (give or take 100,000)

    Creating an index in itself will _not_ cause a recompilation, but when the
    query plan gets recompiled for any other reason the new indexes will be
    taken into account. This means that it is not very useful to create the
    indexes after you have inserted all the data in the table, unless you know
    the query plan is going to be recompiled for some reason after that.

    If you create indexes on a temporary table with 1 million rows, avoid
    creating a clustered index, unless the data you are going to insert in it is
    already in the order of the clustered index. Ordering 1 million rows takes a
    heck of a long time. (well, tens of seconds probably)

    hth

    Jacco


    "Eric Mamet" <eric_mamet_testyahoo.co.uk> wrote in message
    news:11269dcb.0307010813.79b929fdposting.google.c om...
    > If I ask query yser to give me an estimated query plan, how does
    > it work this out since my temp table will only be populated when I
    > fire the query?
    > I suppose this "estimated" plan is only worth so much...
    >
    > Now, what happens at run time, if I create a temp table and populate
    > it with 1 million rows?
    >
    > Is the optimiser "aware" of this 1 million rows or does it make its
    > plan before populating the table?
    >
    > If I create one or several indexes on the temp table during the
    > execution, what does the optimiser does with it?
    >
    >
    > Thanks
    >
    >
    > Eric

    Jacco Schalkwijk Guest

  3. #3

    Default Re: Question about optimizer with (big) temp tables

    Cool, very interesting.

    Thanks a lot: I was about to create my indexes AFTER populating the temp table data!

    Can I force a recompilation by forcing an update of statistics on my temp table?
    Can we do that?
    Eric Mamet Guest

Similar Threads

  1. SP Temp Tables
    By navdeep virk in forum Informix
    Replies: 6
    Last Post: February 18th, 04:30 PM
  2. Union View Optimizer Creates Temp Table
    By Brian Foster in forum Informix
    Replies: 2
    Last Post: September 26th, 10:00 PM
  3. Temp tables and jdbc 2.21.jc3 on IDS 9.4.UC1
    By Rajesh Kapur in forum Informix
    Replies: 0
    Last Post: August 26th, 09:59 PM
  4. Schedule maitenance or temp tables
    By Shawn in forum ASP Database
    Replies: 2
    Last Post: August 21st, 03:12 PM
  5. Accessing Temp Tables
    By Karthik Nagaraj in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 12th, 11: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