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


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

Does a store procedure perform better when all temporary tables are created at first lines

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server
 

I heard something which I could not understand why and how (and if true at all).

I heard that if you create all temporary tables at the beginning (initial lines of the stored proc), the store proc performs better.

I actually thought that it would be better to create the temporary table when (and if) it is used:

Create table #tmp1 (

....)

insert into #tmp1 .....

if not exists (select * from #tmp1)

 return

--(this code is conditional, so #tmp2 is created only if needed - so why isn't this better coding and better performance?)

create table #tmp2 (....

)

---

Now what I just heard (and can't understand), this performs better:

Create table #tmp1 (

....)

create table #tmp2 (....

)

insert into #tmp1 .....

if not exists (select * from #tmp1)

 return --#tmp2 was create for no reason - so how can this solution perform better?

insert into #tmp2....

Thanks,

Dror




View Complete Post


More Related Resource Links

Temporary Tables - MS SQL Server

  
Usage of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in current session while global temporary tables are visible across all sessions.

Temporary tables in SQL Server vs. table variables

  
When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Help!, Problem with creating my store procedure.

  
I have two tables. Product_Table with ProductID as the primary key. My other table, ProductSKU_Table which has SKU as the primary key and ProductID as the foreign key. I am trying to create a store procedure to insert into both tables. How do I insert the primary key from the Product_Table into the foreign key of the ProductSKU_Table? This is what I have so far:ALTER PROCEDURE [dbo].[sp_addNewProduct] ( @productName varchar(50) = null, @shortDescription varchar(100) = null, @longDescription varchar(4000) = null, @active bit = null, @category int = null, @sku varchar(11), @skuDescription varchar(200) = null, @orginalPrice money = null, @sellingPrice money = null, @specialPrice money = null, @qtyOnHand int = null, @qtyAtSupplier int = null, @imagePath varchar(200) = null, @releaseDate datetime ) AS BEGIN DECLARE @lastIDInserted int INSERT INTO Product_Table(Name, ShortDescription, LongDescription, Active, CategoryId) VALUES(@productName, @shortDescription, @longDescription, @active, @category) SET @lastIDInserted = @@IDENTITY INSERT INTO ProductSKU_Table(ProductId, SKU, Description, OrginalPrice, SellingPrice, SpecialPrice, QtyOnHand, QtyAtSupplier, ReleaseDate, imagePath, CreatedDate, ModifiedDate) VALUES(@lastIDInserted, @sku, @skuDescription, @orginalPrice, @sellingPrice, @specialP

Insert into and Store procedure problem

  
Hello I am trying to create store procedure wich will insert data in temp table CREATE PROCEDURE GetDataForUpdate AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDataForUpdate_temp_tb end go WITH cte as (select Sp.[Item No_], Sp.[Starting Date], It.[No_], It.[Manufacturer Code], It.[Description], Sp.[Unit Price], row_number () over (partition by Sp.[Item No_] order by Sp.[Starting Date] desc) as rn from dbo.[Main-db$Sales Price] AS Sp JOIN dbo.[Main-db$Item] AS It ON Sp.[Item No_] = It.[No_] WHERE Sp.[Sales Code]='RETAIL' AND Sp.[Item No_] LIKE 'I%' ), cte2 as ( SELECT [Item No_],SUM(ISNULL(Quantity, 0)) AS Qty FROM [Main-db$Item Ledger Entry] WHERE [Item No_] LIKE 'I%' AND ( [Location Code] = 'WH-AB-#2' ) GROUP BY [Item No_] ) SELECT cte.[Manufacturer Code],cte.[Description], CONVERT(int, cte2.[Qty]) AS 'Qty',CONVERT(int, cte.[Unit Price]) AS 'Unit Price' INTO GetDataForUpdate_temp_tb FROM cte JOIN cte2 ON cte.[Item No_]=cte2.[Item No_] WHERE rn = 1 ORDER BY cte.[Item No_],[Starting Date] But after execute, lefts only this part of query in store procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[GetDataForUpdate] AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDa

View Temporary Tables While Debugging in SQL 2008

  
How can I view temporary tables while debugging in SQL 2008? Note: I do not want to add a SELECT statement everywhere I need to see the contents of the temporary table. I'm hoping there is another way...like can it be done in the command window somehow, etc?

Error when using SQL Server temporary tables with Eclipselink JPA

  
I am using SQL Server 2008 with Eclipselink JPA. SQL Server requires temporary table names to start with # e.g. #AS_ARCHIVE_TMP. When I execute a native query using the table e.g. INSERT INTO AS_ACTIVITY_AR (ACTIVITY_ID,ACTIVITY_TIME,ACTIVITY_TYPE) SELECT ACTIVITY_ID,ACTIVITY_TIME,ACTIVITY_TYPE FROM AS_ACTIVITY WHERE ACTIVITY_ID IN (SELECT ACTIVITY_ID FROM #AS_ARCHIVE_TMP)  I get a warning and then an error because the table name is interpreted as a bind parameter due to the #.  Missing Query parameter for named argument: AS_ARCHIVE_TMP null will be substituted.  Exception EclipseLink-4002 (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: weblogic.jdbc.sqlserverbase.ddc: FMWGENSQLServer JDBC DriverInvalid parameter binding(s). Error Code: 0 Query: DataModifyQuery(sql="INSERT INTO AS_ACTIVITY_AR (ACTIVITY_ID,ACTIVITY_TIME,ACTIVITY_TYPE) SELECT ACTIVITY_ID,ACTIVITY_TIME,ACTIVITY_TYPE FROM AS_ACTIVITY WHERE ACTIVITY_ID IN (SELECT ACTIVITY_ID FROM ?) ") Is there some solution to this problem? Can I escape the table name somehow so it won't be treated as a bind parameter? I have posted the same question on the eclispelink forum, but I am posting it here in case someone else has come across the same issue.  Is it possible to create a temporary table without

SSIS - Call a Store procedure

  
Can someone show me an example of how to call a store procedure that checks for empty fields in a SQL database table, by returning a value when fields are empty. then send an email to the adminstratore.

How to use Store Procedure to execute the Function Of COM DLL (the DLL is Coded By C#)

  
Hi All, I find a question. How to use Store Procedure to execute the function of Dll? I tried as the steps: 1. Code a dll 2. Register the dll by "regasm XXX.dll /codebase" 3. Use the dll in SP But when I do it in step 3, I face some error, which is "Invalid class string" and the error ID is "0x800401F3" Could you help me solve this problem? Thank you very much.

Store Procedure

  
How can i insert and delete a data with use of one store procedure ........... SP File:- ALTER PROCEDURE dbo.One @id int, @Name Varchar(50), @select char(1) AS begin  if @select ='i' insert into j (id,Name) values (@id ,@Name) end begin if @select ='d' delete from j  where id= @id end RETURN How can i called tis two query

How I can to check if Store Procedure altered(been called )?

  
I have ado.net connection to SP               conn = new SqlConnection("Data Source=DY;Initial Catalog=DMSA;Integrated Security=True");                 conn.Open();                   // 1.  create a command object identifying                 //     the stored procedure                 SqlCommand cmd = new SqlCommand("dbo.UpdateCorporationFile", conn);                   // 2. set the command object so it knows                 //    to execute a stored procedure                 cmd.CommandType = CommandType.StoredProcedure;                 cmd.Parameters.Add(new SqlParameter("@count_create_C_W", DBNull.Value));        &nb

debugging stored procedure created in sql server 2008

  
Hi experts,     I want to get rid of old debugging of stored procedure where we use print statement. I know how to debug the stored procedure just wanted to ask, can i get the details of #temp or @tbltemp(TABLE datatype) variable values while debugging

in need of sql 2005 e-book on store procedure

  
i need e-book on sql 2005 express on STORE PROCEDURE TUTORIAL including how to use IF AND ELSE STATEMENT in SQL

debugging stored procedure created in sql server 2008

  
Hi experts,     I want to get rid of old debugging of stored procedure where we use print statement. I know how to debug the stored procedure just wanted to ask, can i get the details of #temp or @tbltemp(TABLE datatype) variable values while debugging

Create store Procedure for select Table by Passing Table Name In Parametere

  
I want to create a Procudure to Select all Information in Table Like (select * from TableName) but Condition is I do not want to Mention The Fix Table Name I will send the Table Name In a parameter from My Vb.net Project like This (select * from @Parameter ) and Then The Select Procudure work with on That Table. It will be change depend on my vb.net reqierment I already Created a Procudure But it Does not working. It is in below. alter proc AllSelect (@Table varchar (30)) as DECLARE @sql nvarchar(MAX) SET @sql = ' Select * from '+@Table+' ' If possible Please let me know also Update and Delete proc by same way. with Regards Suman Bangladesh

How to run a store procedure as background Job ?

  

Hi,

I have scenario where i need to run my store procedure on specific time frame ? For instance i have a field in my table1 called count.

  • If the value of count is 1 for a specific row in table then rightaway the specific row will move to another table2.
  • If the value of count is 2 for a specific row in table1 then the specific row will move to another table2 after 10 min not right away.

i think for this some job should continous run in background, I have store procedure and i want to run that store procedure as background job in MSSQL continous. How can we do that or any other approch? please suggest me.

Thanks
--Jai


JP Sharma

How come the same store procedure call can display different results?

  

Hi all,

This is the sproc I am calling from my Asp.Net application:

ALTER PROCEDURE [Uds].[Dashboard_GetJobs2]
 @date as datetime = null
AS
Begin
 Select * From Uds.RealtimeLogging
 Where (@date is null or [timeStamp] > @date)
End

If I ran from query analyzer I get the correct results:

 exec Uds.Dashboard_GetJobs2 '9/20/2010 11:01:23 PM'

However, if I ran the Asp.Net page, ALL records are showed. Looking at the SQL Profiler I see the following call:
 
 exec sp_executesql N'exec Uds.Dashboard_GetJobs2',N'@date nvarchar(21)',@date=N'9/20/2010 11:01:23 PM'

And if I ran the statement above in query analyzer it WILL return ALL records. Why????


Thank you


JCD

How to check whether a column is been referred in store procedure or not

  

HI Team,

I am about to rename a column in one of the table. How can I check the dependency for the column specially in store procedure? I am using SQL server 2008.

Thanks in advance

 

 


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