473,382 Members | 1,171 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Temp Tables Vs Temp variables

I have an application that I am working on that uses some small temp
tables. I am considering moving them to Table Variables - Would this
be a performance enhancement?

Some background information: The system I am working on has numerous
tables but for this exercise there are only three that really matter.

Claim, Transaction and Parties.

A Claim can have 0 or more transactions.
A Claim can have 1 or more parties.
A Transaction can have 1 or more parties.

A party can have 1 or more claim.
A party can have 1 or more transactions. Parties are really many to
many back to Claim and transaction tables.

I have three stored procs
insertClaim
insertTransaction
insertParties

From an xml point of view the data looks like this
<claim>
<parties>

<info />
insertClaim takes 3 sets of paramters - All the claim level
information (as individual parameters), All the parties on a claim (as
one xml parameter), All the transactions on a claim(As one xml
parameter with Parties as part of the xml)

insertClaim calls insertParties and passes in the parties xml -
insertParties returns a recordset of the newly inserted records.

insertClaim then uses that table to join the claim to the parties. It
then calls insertTransaction and passes the transaction xml into that
sproc.

insertTransaciton then inserts the transactions in the xml, and also
calls insertParties, passing in the XML snippet
Jul 20 '05 #1
2 6387
"Helmut Wöss" <h.*****@iis-edv.at> wrote in message news:<3f***********************@newsreader02.highw ay.telekom.at>...
yes, table variables are faster than temp tables because they are
used like normal local variables, so no lockings are necessary
and they are not created in tempdb (only running in ram)
But there are some other points to take care:
- table variables are not included in transactions, so no rollback possible
- you can't do something like:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements
- and you can't use them in subprocedures (because they are local).


Not strictly true - I believe that th table variables are on hard
drive as appropriate - try sticking a gig of data in one..
Check your query plans as well - the Table Variable dont get
statistics. This may have a relevance for you. I found that
paralellism _seems_ to be destroyed with table variables. Eg
inserting into a #table is fine with loads of parallelism, yet insert
into a seemingly identical @table and the paralelism disapears - in my
case turning the insert into 20 minutes instead of 30 seconds.
Jul 20 '05 #2
Rick Hein (rh***@mutualofenumclaw.com) writes:
I have an application that I am working on that uses some small temp
tables. I am considering moving them to Table Variables - Would this
be a performance enhancement?


In additions to other answers, see also
http://support.microsoft.com/default...b;en-us;305977.

I can confirm WangKhar statement that parallelism is not possible
when you insert into a table variable.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Rebecca Lovelace | last post by:
For some reason in Enterprise Manager for SQL Server 2000, I cannot put the following line into a trigger: select * into #deleted from deleted When I hit the Apply button I get the following...
3
by: Subodh | last post by:
I've written a SP which does some complex calculations and in the end dumps data into 2 tables (master & detail) When I run this sp for smaller no of IDS (employees i.e for 13000 in Master and...
3
by: imani_technology_spam | last post by:
We were trying to remove duplicates and came up with two solutions. One solution is similar to the one found in a book called "Advanced Transact-SQL for SQL Server 2000" by Ben-Gan & Moreau. This...
21
by: Boris Popov | last post by:
Hello pgsql-general, I'm trying to implement a table with rows that are automatically deleted when the session that inserted them disconnects, sort of like our own alternative to...
1
by: msnews.microsoft.com | last post by:
When you step into a stored procedure using vs.net, you get all the debug variables, except temp tables (like INSERT INTO #list (id) SELECT id FROM members WHERE...). Is it possible to view the...
1
by: serge | last post by:
I am running SQL Server Best Practices on a SQL 2000 database and it is recommending me to change the temp tables inside SPs to table variables. I had read already in other places to use table...
16
by: pukivruki | last post by:
hi, I wish to create a temporary table who's name is dynamic based on the argument. ALTER PROCEDURE . @PID1 VARCHAR(50), @PID2 VARCHAR(50), @TICKET VARCHAR(20)
2
by: Burbletrack | last post by:
Hi All, Hope someone can help me... Im trying to highlight the advantages of using table variables as apposed to temp tables within single scope. My manager seems to believe that table...
4
by: R. K. Wijayaratne | last post by:
Hi everyone, I have a SPROC which selects records into a MSSQL #temp table and then selects the records from that. I drag this SPROC onto the right-hand pane of the DBML diagram, but the method...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.