InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 22:54:42 GMT20http://www.sqlservercentral.com/Forums/Topic1422872-392-1.aspx[quote][b]tooba111 (2/21/2013)[/b][hr]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...[/quote]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.Fri, 22 Feb 2013 16:18:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1422872-392-1.aspx[quote][b]Anuj Rathi (2/22/2013)[/b][hr]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.[/quote]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.Fri, 22 Feb 2013 16:16:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1422872-392-1.aspxIf 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.Fri, 22 Feb 2013 02:38:16 GMTAnuj Rathihttp://www.sqlservercentral.com/Forums/Topic1422872-392-1.aspxReally 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.Thu, 21 Feb 2013 23:04:48 GMTmatakhttp://www.sqlservercentral.com/Forums/Topic1422872-392-1.aspxHi 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...Thu, 21 Feb 2013 22:10:55 GMTtooba111