December 8, 2011 at 1:46 am
Hi, here the situation :
i am developping a web application that can be used by many users at time,
i have some stored procedures that return some data, but like i said since the web application (php) can be used by many users at time what to use regarding to the temp tables lifetime and session visibility
for example : what happen if one user execute the stored procedure and by the way create the temp table/ populate it,
and in the same time another user do the same thing
thanks and good day
December 8, 2011 at 2:30 am
What i understand is two persons EXEC same Store Procedure same time....Isit ur qus ?
to ans that let me conform these points.:-)
1.They both EXEC in the same session or same server?
2.Are they just EXEC or doing any modifications?
Need help? Help us help you.
December 8, 2011 at 2:36 am
table variables are good for a very very small result sets
temp tables are good for all result set sizes
if you call your table #temp, then that table is only visable for that one connection and cannot be used by another
if you call your table ##temp then this is a global temp table and can be used by all connections regaurdless of which SPID created the table
table variables are only in the scope of that procedure for that sessions and cannot be passed to other sessions
SQL doesnt actually create multiple tables called #temp, it creates a table with a name like the following #h35gddave4, you should be able to see some under TempDB --> Temporary Tables, which is how it knows which # table belongs to which SPID.
December 8, 2011 at 3:02 am
anthony.green (12/8/2011)
SQL doesnt actually create multiple tables called #temp, it creates a table with a name like the following #h35gddave4, you should be able to see some under TempDB --> Temporary Tables, which is how it knows which # table belongs to which SPID.
Temp tables have a name that just gets lots of _ and a hash added to the end. So CREATE TABLE #temp will create a table with a name something like #temp______________________________________________________________04DA
Table variables get a hex name with no baring back to the actual name given to the variable, so @temp could get a table name like #78DE32A3
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2011 at 3:03 am
It depends what you want.
Do you want each session's data to be separate. If so use @Table variables or #Temp tables in the procedure - they are isolated to the instance of the procedure running, and will be destroyed when the procedure finishes.
If its an expensive procedure that can share data between sessions, its probably best to use a real table. Your procedure can then check the age of the data, if its still valid, just return the table, if its too old, truncate and re-create the data in a transaction.
December 8, 2011 at 3:06 am
GilaMonster (12/8/2011)
anthony.green (12/8/2011)
SQL doesnt actually create multiple tables called #temp, it creates a table with a name like the following #h35gddave4, you should be able to see some under TempDB --> Temporary Tables, which is how it knows which # table belongs to which SPID.Temp tables have a name that just gets lots of _ and a hash added to the end. So CREATE TABLE #temp will create a table with a name something like #temp______________________________________________________________04DA
Table variables get a hex name with no baring back to the actual name given to the variable, so @temp could get a table name like #78DE32A3
Thanks for the correction Gail, much appreciated.
December 8, 2011 at 3:22 am
yes i want to use this in a web application used by many users
so each user is on his machine/browser ...
an important thing to add,
i want to use the temp table in another join sql query , so:
- execute the stored the temp table
- execute a join query that invoke that temp table, since in sql server unlike firebird we cannot use the result stored procedure directly in another query ...
thanks for your support !
really appreciate 🙂
ps : this stored procedure return really really few results based on user input parameters
December 8, 2011 at 3:33 am
There are ways of joining the output of a stored procedure to another table. One way would be to use ##Temp tables
Another would be to use INSERT ... EXEC something like this (From NorthWind)
CREATE PROC myCustomers
AS
SELECT CustomerId, CompanyName, Address
FROM Customers;
DECLARE @MyCustomers TABLE (CustomerId nchar(5) PRIMARY KEY,
CompanyName nvarchar(40),
Address nvarchar(60));
INSERT INTO @MyCustomers
EXEC myCustomers
SELECT C.*, O.Freight
FROM @MyCustomers AS C
JOIN Orders AS O ON C.CustomerId= O.CustomerID;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply