Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » View Vs TempTable..
Add to briefcase

View Vs TempTable.. Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
tooba111
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
matak
Posted Thursday, February 21, 2013 11:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 05, 2013 7:23 PM
Points: 133, Visits: 1,060
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
Anuj Rathi
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, March 22, 2013 3:01 AM
Points: 93, Visits: 163
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
Jeff Moden
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: Yesterday @ 2:32 PM
Points: 32,906, Visits: 26,792
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1423287
Jeff Moden
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: Yesterday @ 2:32 PM
Points: 32,906, Visits: 26,792
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1423288
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.