Post reply

Two questions in mind (for genralized approach)

  • May 18, 2011 at 9:33 am

    #236567

    Hello Folks,

    While working on some optimization stuff, i found confusion over some basics. Please let me know if there are any basic approaches to look forward for them.

    1. first create temp table, then create clustered index and finally insert data into it.

    Vs

    Directly insert data into a temp table and then create clustered index.

    Which one should be faster and which approach should we follow ?

    2. I've join of more than two tables like this

    select a.c1, a.c2........

    fromT1a

    inner join T2bon a.c1= b.c1

    and a.c2 = b.c2

    inner join T3con a.c1 = c.c1

    and a.c2 = b.c2

    wherea.ca = @c1

    Vs

    select a.c1, a.c2........

    fromT1a

    inner join T2bon a.c1= b.c1

    and a.c2 = b.c2

    and a.c1 = @c1

    and b.c1 = @c1

    inner join T3con a.c1 = c.c1

    and a.c2 = b.c2

    and a.c1 = @c1

    and b.c1 = @c1

    wherea.ca = @c1

  • May 18, 2011 at 9:42 am

    #1326576

    Select into could cause recompiles and slow you down.

    I only use it in dev mode where the list of columns is ever changing. Then at the end of the cycle I switch to create table.

    The only time I've left this is prod was when I had a very dynamic search proc with very complex logic and ever changing columns. I decided to keep it at select into (first query at the top) to keep my sanity. If there was a hit it wasn't very noticable and I wasn't getting recompiles so I left it alone.

  • May 18, 2011 at 9:44 am

    #1326581

    For #2 the optimizer is smart enough to understand what you're doing. There's no point in giving the same command over and over again.

    I've heard this myth too and seen it work in sql 2k. I've never rechecked in 2k5 so I'll leave this open to others.

  • May 18, 2011 at 10:28 am

    #1326622

    Thanks for the quick reply. Regarding your first point, does it mean that following the first approach is better one ?

    On Second Point, I want to clear is whether the joins between the tables first takes place and then the condision based on WHERE cluase is applied ? In that case we'll be having the dataset with all the values of a.ca from the joinset and finally the rows with a.ca = @c1 will be selected. Hope you got my question.

  • May 18, 2011 at 10:31 am

    #1326626

    sqlnaive (5/18/2011)

    On Second Point, I want to clear is whether the joins between the tables first takes place and then the condision based on WHERE cluase is applied ?

    Nope.

    Run the queries, check the execution plan, that will show you how the work is being done.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 18, 2011 at 10:37 am

    #1326634

    I generally do create table, create indexes, then insert.

  • May 20, 2011 at 1:01 am

    #1327497

    Thanks Gail. Nice idea. I'll check that out with execution plan and surely post my findings to this post.

    Ninja, though i liked your idea, but still will test the same with execution plan as well and gt that posted as well. 🙂

  • May 20, 2011 at 1:25 am

    #1327501

    sqlnaive (5/20/2011)

    Thanks Gail. Nice idea. I'll check that out with execution plan and surely post my findings to this post.

    Ninja, though i liked your idea, but still will test the same with execution plan as well and gt that posted as well. 🙂

    I had recommend you to read grant's execution plan books.

    It's really good book for performance tuning and optimization.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • May 20, 2011 at 6:04 am

    #1327588

    muthukkumaran (5/20/2011)

    sqlnaive (5/20/2011)

    Thanks Gail. Nice idea. I'll check that out with execution plan and surely post my findings to this post.

    Ninja, though i liked your idea, but still will test the same with execution plan as well and gt that posted as well. 🙂

    I had recommend you to read grant's execution plan books.

    It's really good book for performance tuning and optimization.

    Got the link for it? I can't find it anymore.

  • May 20, 2011 at 6:55 am

    #1327643

    Ninja,

    I know you are asking this link for some other(coming) pepoles 🙂

    http://www.amazon.com/s/ref=nb_sb_ss_i_0_14?url=search-alias%3Dstripbooks&field-keywords=grant+fritchey&sprefix=grant+fritchey

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • May 20, 2011 at 6:59 am

    #1327648

    I don't know why but I was under the impression that there was a free ebook version of 1 on his books.

  • May 20, 2011 at 8:55 am

    #1327738

    Ninja's_RGR'us (5/20/2011)

    I don't know why but I was under the impression that there was a free ebook version of 1 on his books.

    There is. See the Books item in the menu to the left.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 20, 2011 at 9:22 am

    #1327756

    GilaMonster (5/20/2011)

    Ninja's_RGR'us (5/20/2011)

    I don't know why but I was under the impression that there was a free ebook version of 1 on his books.

    There is. See the Books item in the menu to the left.

    Yes gail here is the link

    http://www.sqlservercentral.com/articles/books/65831/

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • May 20, 2011 at 9:26 am

    #1327761

    GilaMonster (5/20/2011)

    Ninja's_RGR'us (5/20/2011)

    I don't know why but I was under the impression that there was a free ebook version of 1 on his books.

    There is. See the Books item in the menu to the left.

    Thanks, both of ya.

    And no gail doesn't have multiple personalities. :w00t:

  • May 20, 2011 at 9:33 am

    #1327768

    Ninja's_RGR'us (5/20/2011)

    GilaMonster (5/20/2011)

    Ninja's_RGR'us (5/20/2011)

    I don't know why but I was under the impression that there was a free ebook version of 1 on his books.

    There is. See the Books item in the menu to the left.

    Thanks, both of ya.

    And no gail doesn't have multiple personalities. :w00t:

    😀

    Ninja may i know your real name.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

Viewing 15 posts - 1 through 15 (of 24 total)

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