Temp table performance tunning | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Temp table performance tunning

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored procedure to a calling stored procedure? Can we avoid temp tables in this case?
2. Are operations in a stored procedure are treated as a transaction? Any help will be greatly appreciated. Background: We need to use temp table to pass recordsets from a nested stored procedure to a calling stored procedure. Our understanding is that in this case, we have no choice but to use temp tables. So, we need to optimize the performance as much as possible. To do this, we wanted to find out whether operations in a stored procedure are treated as a transaction. We are using SQL 2000 SP4. I could not find any answers so I did the following experiment. Experiment 1:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Wiz_SP_Transaction_Test]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS Update
Articles
SET
IsUpdate = 20
where
ArticlesId < 80000 SELECT * from Articles GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO “SELECT * from Articles” takes a long time (about 40 seconds) to complete Before executing the SP, the IsUpdate attribute for all articles is 30. Then I executed this SP. Before the SP is finished, I end the SP manually. I checked the IsUpdate attribute again, and found that all Articles#%92s (ArticlesId < 80000) Isupdate attribute is now 20. The operations did not rollback. I interpret this to mean that the whole SP is not treated as a transaction. Then, I did experiment 2 below. This time, I explicitly declared the transaction. SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Wiz_SP_Transaction_Test]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS
BEGIN TRANSACTION
Update
Articles
SET
IsUpdate = 50
where
ArticlesId < 80000 SELECT * from Articles IF @@ERROR <> 0 ROLLBACK TRANSACTION
COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO Before this second SP, the IsUpdate attribute is 20 (set in the first experiment). I run this second SP and ended it manually before it finished. I checked the IsUpdate attributes for all Articles#%92s (ArticlesId < 80000), but their Isupdate attribute is 50. So the operation did not rollback either. But we have declared the transaction explicitly. Does this mean that the SP is still not treated as a transaction?

ad 1: Check this out:http://www.sommarskog.se/share_data.html
ad 2: Look up "implicit transactions" im BOL. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Thanks!
]]>