Use Table Data Type instead of Temp Table
-
Hello !
I'm beginer in T-SQL.
I want to use Table Data Type instead of Temp Table in Stored Procedures.
For example:
in a procedure like this:
CREATE PROCEDURE [StocProdStive]
(@IdProd [int])
AS
SELECT ProdCod.c_prodcod, ProdCod.codBara, SUM(Stive.cubaj) AS cantIntr
INTO #IntrProd
FROM Produse INNER JOIN
ProdCod ON Produse.c_prod = ProdCod.c_prod INNER JOIN
Stive ON ProdCod.c_prodcod = Stive.c_prodcod
WHERE roduse.c_prod=@IdProd">Produse.c_prod=@IdProd
GROUP BY ProdCod.c_prodcod, ProdCod.codBara
SELECT ProdCod.c_prodcod, SUM(BonConsDet.cant) AS cantIesBon
INTO #IesBon
FROM ProdCod INNER JOIN
BonConsDet ON ProdCod.c_prodcod = BonConsDet.c_prodcod
WHERE ProdCod.c_prodcod IN
(SELECT #IntrProd.c_prodcod
FROM #IntrProd)
GROUP BY ProdCod.c_prodcod
SELECT ProdCod.c_prodcod, sum(FactIesdet.cant) as cantIesFact
INTO #IesFact
FROM ProdCod INNER JOIN
FactIesdet ON ProdCod.c_prodcod = FactIesdet.c_prodcod
WHERE ProdCod.c_prodcod IN
(SELECT #IntrProd.c_prodcod
FROM #IntrProd)
GROUP BY ProdCod.c_prodcod
SELECT #IntrProd.codbara, #IntrProd.cantIntr,
#IntrProd.cantIntr-ISNULL (#IesBon.cantIesBon,0)-ISNULL (#IesFact.cantIesFact,0) AS stoc
FROM #IntrProd LEFT OUTER JOIN
#IesBon ON #IntrProd.c_prodcod=#IesBon.c_prodcod
LEFT OUTER JOIN
#IesFact ON #IntrProd.c_prodcod=#IesFact.c_prodcod
WHERE #IntrProd.cantIntr-ISNULL (#IesBon.cantIesBon,0)-ISNULL (#IesFact.cantIesFact,0)<>0
ORDER BY #IntrProd.codbara
GO
In fact, I want to send the result of SELECT comand into a Table Data Type like above.
It is posible this?
10x
Liviu
-
It is possible but, what is the purpose of the table variable or #table once you are done with it? How many rows are going to be placed into it?
If the amount of rows is excessive (to me over 50 rows in a table variable) or there will be continual heavy processing of the data AFTER being placed in the table variable then stick to #tables.
There is a line that once crossed you get no performance increase of the use of table variables because they write to disk as well.
just food for thought
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Don't use SELECT INTO. It is an expensive way to do it.
DECLARE your table variable or create your temporary table then use INSERT ... SELECT
I would also split out your stored procedure so that you have
- a stored procedure that creates your temporary tables
- a stored procedure that does something with your temporary tables
- a master stored procedure that calls both of the above.
After the CREATE PROC....AS statement I always put SET NOCOUNT ON as this suppresses row count status messages and therefore speeds up the query.
-
Thanks for answers.
I call this procedure from VFP with EXEC stocProdStive [parameter]. It's return less then 100 rows
It is easy for me to execute all in one procedure without split it. It's only an example. But in other I want to use UPDATE or another commands.
I read that Table variable works faster that temporary table. That is the reson to use this kind of variable.
Can I use DECLARE table variable without declare the fields structure or get this structure from a SELECT command, something, to avoid write field by field and do this dynamically.
10x a lot !
Liviu
-
Thanks for answers.
I call this procedure from VFP with EXEC stocProdStive [parameter]. It's return less then 100 rows
Unless you add structures to the #Temp table that the @Temp table can't support and therefore can't leverage I think the @Temp table should always be faster due to the lack of logging. Its just that a difference of 13 milliseconds on a 1500 millisecond process is not important. The more work being done the less the significance of the performance gains. My experience is that the performance benefit is not a constant percent. I.E. a 30% gain with 50 rows may be a 1% gain at 500 rows and a 0.1% gain at 10000 rows. So, unfortunately, the more you crave the benefit the less benefit you get. I believe this is basically the point that AJ was making.
It is easy for me to execute all in one procedure without split it. It's only an example. But in other I want to use UPDATE or another commands.
I think what David was suggesting is that if you are (or may in the future) re-use the same temp table structure you may want to split the create from the actual uses. That way there would be different master SPs that reused the creates and a subset of the various access SPs. If there was another reason I'd be interested to learn it.
I read that Table variable works faster that temporary table. That is the reson to use this kind of variable.
There are some limitations, and then the marginal gain for larger workloads mentioned above. If the workload pushes the limits of the performance gain benefits then you are likely best off with the more flexible and powerful #Temp table. But then again you have to actually be sure to DROP those. The main limitations I can think of is that not all indexing is supported by @Temp tables (clustering and alternate indexes?) and @Temp tables are not visible across batches. That means that the multiple SPs approach discussed above requires #Temp tables instead of @Temp tables. It also means that if you need to use it with crossing into or out of dynamic SQL the @Temp table would not work.
Can I use DECLARE table variable without declare the fields structure or get this structure from a SELECT command, something, to avoid write field by field and do this dynamically.
As mentioned above, @Temp tables can not cross batches, and so if you used dynamic SQL you would A) take a performance hit for using dynamic SQL and B) you would need to place the entire SP content into the dynamic SQL, not just the declare part.
-
as I understand it both #temp tables and @table vars may be created in memory or disk -depends on size and how much memory you have available I guess.
Using a table var avoids any recompiles of the proc created by #temp tables. When table vars first arrived I looked into the diffs with #temp tables and sometimes the table var would produce significantly more i/o. With smallish datasets table vars seem to work best, and they will take a PK declaration so you can apply some indexing. There is one alternative, which may or may not be of any advantage, and that is to create permanent tables in tempdb, dropping them on the way out of the proc.
btw .. select into a #temp table to create the table should be avoided at all costs
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Using a table var avoids any recompiles of the proc created by #temp tables.
How does creating a #temp table impose a recompile of the SP? I know that it sounds like I'm contesting it but I'm not. I just don't know what you are referring to and feel it may be valuable to understand.
btw .. select into a #temp table to create the table should be avoided at all costs
Until this thread I didn't even know about the SELECT INTO synax (or else I blocked it) and the idea of creating a table that way. I can see maintenance/security reasons for not creating user tables with it, and David mentioned that it is "expensive", but if there is a quick explanation of why it is expensive I'd appreciate knowing it so that when a co-worker comes to me and says "Hey! I just discovered this cool SQL thing!" I will have a better counter arguement than "it is supposedly 'expensive'".
Thanks for your help.
-
there are a number of articles from ms about things which cause procedure recompiles during execution, #temp tables are one of them see Q243586
select into can cause all sorts of problems with spawning locking threads, cxpacket locks and causing locking within tempdb. It was a major cause of many problems at my current placement. There are articles on the site about these I think.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply