Post reply

View Vs TempTable..

  • February 21, 2013 at 10:10 pm

    #271240

    Hi Guys,

    I am having a problem with my SP, taking extra time to execute. I am using VIEW that has I believe 334k rows. I am wondering what do you guys think its a good idea if i create #temp table and Create Index On it and use this temp table all over Instead of View. I can't create index on View.

    Do you guys think its improve the performance or worse.

    Any advise?

    Thanks in advance...

  • February 21, 2013 at 11:04 pm

    #1590201

    Really without seeing any form of DDL and the actual view + proc its going to be impossible to say one way or the other.

    Attaching a .sqlplan file will be helpful too.

  • February 22, 2013 at 2:38 am

    #1590247

    If your view contains data from several tables & you have to use that view in several queries, then It is always better to create views.

    Views does not have any data. It is only a virtual table.

    One more thing, don't use any where clause in views until it is necessary.

    You can create indexed views. (personally I never prefer temp tables).

    As "matak" said, if you can provide execution plan, then it is very useful to identify the exact problem.

  • February 22, 2013 at 4:16 pm

    #1590553

    Anuj Rathi (2/22/2013)

    If your view contains data from several tables & you have to use that view in several queries, then It is always better to create views.

    Views does not have any data. It is only a virtual table.

    One more thing, don't use any where clause in views until it is necessary.

    You can create indexed views. (personally I never prefer temp tables).

    As "matak" said, if you can provide execution plan, then it is very useful to identify the exact problem.

    I have to say that I strongly disagree. While views are certainly a handy way of encapsulating common code, the use of a Temp Table to Divide'n'Conquer much larger queries frequently returns performance that will astound even some seasoned T-SQL programmers. If you're not using Temp Tables because you believe that Temp DB shouldn't be used by queries, guess again. SQL Server is usually going to use Temp DB for all but the most simple of queries and for large queries with many joins, it may use Temp DB much more than you would with a Temp Table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 22, 2013 at 4:18 pm

    #1590554

    tooba111 (2/21/2013)

    Hi Guys,

    I am having a problem with my SP, taking extra time to execute. I am using VIEW that has I believe 334k rows. I am wondering what do you guys think its a good idea if i create #temp table and Create Index On it and use this temp table all over Instead of View. I can't create index on View.

    Do you guys think its improve the performance or worse.

    Any advise?

    Thanks in advance...

    The view itself may be the problem and a slight tweak to it may help immensely. Please see the second link in my signature line for how to post a performance problem to help us make a better recommendation to you.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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