Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

View Vs TempTable.. Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 10:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:10 PM
Points: 25, Visits: 112
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...
Post #1422872
Posted Thursday, February 21, 2013 11:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:20 AM
Points: 235, Visits: 2,182
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.
Post #1422884
Posted Friday, February 22, 2013 2:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
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.
Post #1422935
Posted Friday, February 22, 2013 4:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:23 PM
Points: 35,403, Visits: 31,965
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423287
Posted Friday, February 22, 2013 4:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:23 PM
Points: 35,403, Visits: 31,965
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423288
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse