.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Performance Drop when switching from a #temp table to a @temp table variable

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server
 

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim




View Complete Post


More Related Resource Links

Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


What is the difference performance wise in creating a temp table using declare @table table or creat

  

Hi

I have a query that is taking forever to run on the production server.

Do you see a reason why will it take forever to insert it to a temp table... If i put my query in a cte it works fine...

This procedure was using a table variable and i changed it to a temp table but it didnt help performance wise

I found out that when i just do the select statement it works the same amount of time that cte takes but when it inserts it a temp table (around 75 rows) it takes forever.

Any help will be appreciated.

Thanks

Karen


Dynamic Column filter on table variable or temp table

  

Hi,

I created a  procedure which first populates a table variable /temp table with data. Then based on input parameter (say, @ColumnNames) I want to filter the result set.

Here is the snippet I have created, -

create PROCEDURE [dbo].[GetAlerts]
	@roleId varchar(20),
	@fields sysname
AS
BEGIN
declare @temp table(
	ID int,
	SOURCESYSTEMNO int ,
	role_id varchar(20),
	CRITICALINFO varchar(1000));

    insert into @temp select * from dbo.commonalertdata where role_id= @roleId);

	declare @sql varchar(300);
	set @sql='select ' + @fields + ' from @temp';
	exec dbo.sp_executesql @sql; 
END

Then i tried to run it as follows, -

exec dbo.GetAlerts 1 , 'SOURCESYSTEMNO, CRITICALINFO'

And It gave the following error.

Msg 1087, Level 15, State 2, Line 1

Must declare the table variable "@temp".

Now, I tried using table variable or temp table or even UDF with no good. Can anybody help me in getting dynamic columns from a calculated table valued result set like temp table / table variable / inline table valued UDF?


Invalid object error on temp table on a particular environment

  
Hi, I am using a temp table in my sp. I have written the following tsql before and after using the temp table. IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE tempdb..##TEMP; Data will be inserted to the temp table consecutively using a cursor. I use SELECT * INTO tempdb..##TEMP FROM EXEC('query') to fill this temp table for the first time and INSERT INTO tempdb..##TEMP(<fields>) EXEC('query') for the remaining times. The SP is working fine in my laptop, but I am getting an Invalid Object error on this temp table name in the production server. Could anyone tell me the possible causes please? Thanks Sree

Oracle ref cursor or temp table?

  
I have to return a large amount of data from a complicated query to an asp.net web page. It is currently doing it by running a procedure to fill an Oracle temp table and pulling all of the data from the temp table for display. I was wondering if using a ref cursor would be faster and more efficient. Does anyone know?

Update producing invalid object name for temp table

  
If IWantThisDSN = 0 Then SQLString = "SELECT importparamsID, ESubmitType, Description, ImportFileName,ClientCode, Notes00, Notes01, Notes02, Notes03, Notes04 into #t0 from Importparams " Else SQLString = "SELECT importparamsID, ESubmitType, Description, ImportFileName, ClientCode, Notes00, Notes01, Notes02, Notes03, Notes04 into #t0 from Importparams " End If cn.Execute(SQLString) SQLString = "UPDATE #t0 Set Description = ' ' WHERE Description IS NULL" cn.Execute(SQLString) I'm getting the error in the last cn.Execute(SQLString) line...   Message=[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#t0'. I'm using VB 2010 and SQL Server 2008. This worked fine before with VB 6 and SQL Server 2000 but with the "upgrade" it's not working... If you need any more info let me know what I may have left out.  Thanks!

insert mulitple comma seperated values in a temp table

  
I have a reporting services report with a single parameter that can take up to 10 numbers which are comma delimited. I need to insert those numbers into a temp table with one column. So if the numbers were 1 - 10 I would expect to see something like this if querying the temp table Acct_Num 1 2 3 4 5 6 7 8 9 10 Is there a way to do this insert into the table using a comma delimeted string of numbers? ThanksFJK

Executing sql statment stored in temp table (without variables)

  

How could I execute a sql statement stored in a temp table? something like

 

select

 

'select * from sysobjects' as test into #tmp

exec

 

(sel

create a temp table using a name passed as a param

  

I am trying to move VB script to sql server and I am not sure how to do this one:

create proc CreateTable(tempTable as varchar(50)
)
AS

SET @TempTble = 'tempdb..' + @tempTble

IF object_id(@TempTble ) IS NULL
 BEGIN
  CREATE TABLE @tempTble
  (
   id INT, fldName NVARCHAR(255), Balance FLOAT, 
   SName NVARCHAR(100), eventDate Datetime
  )
 END


Getting null when using dm_db_partition_stats with #temp table

  

drop table #TEMP
select * into #TEMP from tblEmp
Select SUM(row_count) from sys.dm_db_partition_stats where object_id = OBJECT_ID('dbo.#TEMP')
AND (index_id = 0 or index_id = 1)

Breaking long query into small one using SQLDataSource (using Temp table)

  

Currently, I have long script which does major processing and then puts data into temp table.

Finally I read data from this temp table to show on the chart, based on user's selection of parameter. So I can divide my query into two parts and while I am researching this, I will appreciate any pointers/guidance.

 

Is it possible to do processing in one Datasource and read table from second datasource?

Is it even possible?

If not, what is recommended method for this (breaking large processing into small one)?


temp table or querystring

  

Hi,

According to what my needs it is better to use temp table or querystring?

Click data in DbGrid and redirected to another Web site with ID data.

ID I need, the need for ID data stored in the table.

I thought even the temp table, which would temporarily stored ID and you would then find all the WHERE clause.

What is the best solution for this?


Regards


SSIS importing from a global temp table to another database

  

I have a work flow as followed but haven't been had any success after the 1st step.

1. run a query to create a global temp table from ServerA.databaseX

2. pull the data from the global temp table to the ServerB.databaseY

I think the problem is that after the step1 excuted, the session closes and the global temp table gets deleted. So when the step 2 tries to access the global temp table it's no longer there.

I tried to pull the data using the direct query method (no temp table) but it took too long - more than 45 minutes. If i have to do the above steps manually, it wouldn't take more than 15 minutes.

I dont have access to create a local table in the destination server/database.

Is there a way to keep the session alive so i can pull data from the global temp table.

I appreciate any help you can provide.


temp table in linq

  
select col1,count(col2) into #temp1 from table1
where col3='asd' and code =@code
group by col1


select --------
from table2 t2
inner join table3 t3 on t2.c1=t3.c1 -----------
left join #temp1 t4 on t3.c1=t4.c1
 
I'm not sure how to achieve the same in the Linq.
Or is there any better way?



 


Store and retrive data from a temp table

  

Hi,

I want to create a temp table to store data in a stored procedure and then then retrive it from the code.
I need to save the data in the begining before it is deleted.And then after the inserts are done I want to update the tblcontactlist by referring to the temp table.

Can someone help me with this please?


 

Private Sub btnAssign_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAssign.Click
        Dim bUpdate As Boolean = True

        Try
            Dim strSql As String
            Dim strSql1 As String
            Dim dr As SqlDataReader
           

            'delete exist CIM Employees
            strSql = "delete from tblBNIContactList where companyid=" & txtCompanyid.Text
            da.UpdateData(strSql)


            'Assign Employee
            Dim i As Integer
            For i = 0 To lbxSelectedEmployees.Items.Count - 1
                strSql = "insert into tblContactList(CompanyID, IssuePrefix, UserID, PM, DevMgr, AltIncAllCorr) values("
                strSql &= cboCompanyName.SelectedValue & ",'" & txtIssuePrefix.Text & "', '" & lbxSelectedEmployees.Items(i).Value & "', 0, 0, 0)"
                da.UpdateData(strSql)
            Next

   

Can I have 3 different tables in a report? And, can I pass 3 temp table to SSRS as source for these

  
I'm using BI 2008 and sql 2005 and 2008.  Is there an example to show me?  Thanks.
Lapucca

T-SQL query to insert in Temp Table

  
I need an expert suggestion on a SQL problem.The scenario is as follows

" I have a temporary table which has 10 rows and it is ordered by Name.
  I need to insert a row which should get inserted as the 1st row and the order by should not apply to the same "

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend