@table VS #table

WHIch one is the best to use in my SP's.I have lots SPs which are meant to calculate huge data,so in those SPs right now i am using lots of #tables and i found that my server performance is effected by those #tables. So i wanted to use @.tables henceforth.
Please guide me in this issue to choose the best one...

Thanks.How much data are you storing in the temp tables?

Temp tables will use tempdb

table variables will use memory...

both use up resources...

I believe BOL recommends table variables...

What are you doing?

Would it make sense to use physical staging tables?|||maximum of 300 rows and each row having 10 columns and I run most of the SPs for every ten mins as a job.

Thats the whole Picture.

Thanks.|||With a 300 by 10 chunk of data, you aren't talking enough to get excited about either way. Table variables (@.table) will give you faster scans, and temp tables (#table) will give you faster seeks. Most boxes I've seen have more disk than RAM, but either way your load isn't big enough to matter.

I'm just curious, what makes you think that the #tables are affecting your server performance? Even if there were a couple dozen of them, they shouldn't be noticable. A few thousand would have some impact, but I doubt that you've got anywhere near that volume.

-PatP|||Another consideration is that you can put as many indexes as you want on a temporary table to increase JOIN efficiency, but on a table variable you can only have one index.

I generally use table variables unless the dataset is going to be accessed multiple times on a non-indexed column.|||another day, learnt another thing about sql server feature.
thank you guys.

:)

ps: i am always excited about new things that i learn.|||Originally posted by bruce_Reid
WHIch one is the best to use in my SP's.I have lots SPs which are meant to calculate huge data,so in those SPs right now i am using lots of #tables and i found that my server performance is effected by those #tables. So i wanted to use @.tables henceforth.
Please guide me in this issue to choose the best one...

Thanks.

It will vary by the system also.

In our current system (we have our RAM max'd out though), we cut it off at 10k records as a general rule. If we join the table to a lot of tables later on in the procedure though, we will use temp tables to take advantage of the indexes. You need to find out what's good for your system.

BTW, if you do a trace, you will find that table variables create a space in tempdb and use that. It will be something like #12@.1aadfa0.

The difference is how the memory space is used vs. the ability to use indexes.|||With a table variable (never used one, But was about to use it to implement some sorta virtual queue for each user of a multi user environment),

1. is it possible to define an identity column? (tried it without success)

2. is it possible to select a subset of a table into the table variable? i.e. with out using an insert statement, but rather something more like
SET @.table_name = (SELECT col1, col2, col3 FROM TABLE1)
I tried this without success.

James :)|||Originally posted by nano_electronix
With a table variable (never used one, But was about to use it to implement some sorta virtual queue for each user of a multi user environment),

1. is it possible to define an identity column? (tried it without success)

2. is it possible to select a subset of a table into the table variable? i.e. with out using an insert statement, but rather something more like
SET @.table_name = (SELECT col1, col2, col3 FROM TABLE1)
I tried this without success.

James :) 1. Yes, you can, as in:DECLARE @.t TABLE (
id INT IDENTITY
, v VARCHAR(20) NOT NULL
)

INSERT INTO @.t (v) VALUES ('One')
INSERT INTO @.t (v) VALUES ('Two')
INSERT INTO @.t (v) VALUES ('Three')

SELECT * FROM @.t

2. Not as you've shown it, but you can sneak up on it using the INSERT INTO syntax.

-PatP|||Indexing temp tables? It'll be a wash because of the possibility of recompiles and locking of sysobjects, sysindexes, and syscolumns in tempdb. I've never indexed a temp table because I've never had them big enough to bother. If it turns out to be of significant size, maybe using temp table was not the best choice? Percentage of table scan on it should be minimal compared to overall cost of the query, and THIS should be the main determinant whether to use temp table or not. And as far as a difference between # vs. @., - I'd use # only if I want to store a result of an " insert #tmp execute sp", otherwise - it's definitely @..|||I've gotten performance boosts from indexing table variables and temp tables, but only if they are referenced multiple times in a procedure. I normally use table variables now, but will revert to temp tables when I need additional indexes that can't be added to a table variable.|||We're the same way blindman. We do have some batch processes that run at night though where we have received huge performance gains from indexed temp tables. Usually they have 75k-700k records though and are joined several times.

The performance increase on anything over several thousand can be mind-boggling by just building an index on the temp table.|||Originally posted by Pat Phelan
1. Yes, you can, as in:DECLARE @.t TABLE (
id INT IDENTITY
, v VARCHAR(20) NOT NULL
)

INSERT INTO @.t (v) VALUES ('One')
INSERT INTO @.t (v) VALUES ('Two')
INSERT INTO @.t (v) VALUES ('Three')

SELECT * FROM @.t

2. Not as you've shown it, but you can sneak up on it using the INSERT INTO syntax.

-PatP

Cheers

For answer to question 2, did u mean something like ...

INSERT INTO myTable99(Col1)
SELECT '1~2~3~4~5' UNION ALL
SELECT '1~2~3~4~5' UNION ALL
SELECT '1,2~3,4,5'

I pulled it above off another thread. hehehe.

James :)|||I think the question was, can you use SELECT INTO to create a table variable the same way you can create other tables on the fly, and the answer is no. Table variables must be explicitly defined, though I'm not sure what Pat meant by "sneaking up on it". Pat?|||Is this possible?

DECLARE @.t TABLE (
col1 VARCHAR(20),
col2 VARCHAR(20),
col3 VARCHAR(20)
)

INSERT INTO @.t
(col1, col2, col3)
values
(SELECT col1, col2, col3 FROM src_table)

sorry i have to ask instead of trying it out myself, main reason is because i had a fresh install which i want to ghost before i do anything to it and i don't want to import any sample database into the freshly installed database.

I guess the question i am asking about is -
"Is it possible to do multiple insert like how i wrote the insert query above?"

cheers

James :)|||I don't think so. You can write it like this instead:

INSERT INTO @.t
(col1, col2, col3)
SELECT col1, col2, col3
FROM src_table

and that should run just fine.|||Thank you that's the answer i needed.

I would imagine that's not standard ANSI either?

James :)|||ummmm, that's about as standard as it gets.|||Originally posted by derrickleggett
ummmm, that's about as standard as it gets. The tabel variable is 100% Microsoft extension, the INSERT INTO...SELECT syntax is ISO/ANSI standard.

In response to blindman's question, that is exactly what I meant by "sneaking up" on a table assignment, using the INSERT INTO syntax.

-PatP|||Originally posted by Pat Phelan
The tabel variable is 100% Microsoft extension, the INSERT INTO...SELECT syntax is ISO/ANSI standard.

In response to blindman's question, that is exactly what I meant by "sneaking up" on a table assignment, using the INSERT INTO syntax.

-PatP

The SQL ANSI-92 standard has to do with usage and syntax. The statement is as standard as it gets.