What's better for performance... -- sql server (microsoft)

aaron.kempf NO[at]SPAM gmail.com
12/14/2006 2:01:43 PM

depends on SMP count and other things.

I'd recomend temp tables in most situations; because @table variables
are limited to a single processor.. and processor count is going UP
faster now than any other time in history-- next year the common db
server might be a dual socket quad core.. so the penalty for @table
variables goes up over time

(because of high cpu/core counts)

-Aaron

[quoted text, click to view]

Martin
12/19/2006 6:45:15 AM

Would this also be the case for SQL 2005 CTEs? Do they essentially use
table variables internally?

I changed some code on my laptop to use a CTE and it ran much quicker.
However on the mutli-proc server, it actually ran slower than the temp
table version.

Martin

[quoted text, click to view]

jebuskrust NO[at]SPAM gmail.com
12/19/2006 11:35:18 AM

1 - Next question: Are table variables stored in memory or are they
stored in tempdb like #temp_tables?

Which is faster to query..

Say I Populated data between 1-100 and stored it into a @table variable

Like so:

insert into @table
1
2
3
4
.... 100

And ran select * from @table
Or

Say I Populated data between 1-100 and stored in a #temp table
Like so

insert into #temp
1
2
3
4
.... 100
select * from #table
drop table #table

Comments or suggestions are appreciated..

[quoted text, click to view]

aaron.kempf NO[at]SPAM gmail.com
12/19/2006 1:39:59 PM

table variables are not written to tempDB.

I just think that it's difficult to use these because you can't persist
a connection in ADO.net

in 80% of the cases; using insert into @table would be faster-- becasue
you're using it for writes and reads.

using it for writes is always faster for table variables.. I think..
because you don't have logging for example.

I think that if you had 100 users doing this; table variables would be
a better choice.. but I think that they can use up a lot more memory.

I personally dislike table-valued functions and all that crap.. I just
don't trust them.

but yeah; if you were running 100 inserts and then a read; I believe a
table variable would almost always be faster

for other situations-- maybe more real world type solutions; I think
that there are a lot of reasons to avoid table variables.. like
overhead and latency.. I just think that if you've got a properly
configured tempdb (seperate array; raid 0?) then you'll have adequate
performance either way.. and I just have a gumption that table
variables are more reliable-- since we've been using them for 10+ years
and table variables aren't that old by a long shot

I dont think that you can pin table variables.. and i think that table
variables are less memory efficient than temp tables... and I think
that table variables are less capable of disk caching and sql server
memory caching than table variables

I think that the ability to write an index on a temp table is not
something that you should overlook.. an index on a small table might
increase performance dramatically depending on the type of operation
that you're doing

there's a good article at sql mag about temp tables vs table variables
I think that they were maybe a 7 on a scale of 1 to 10 about tmp tables
(1) to table variables (10).

I think that I'm move like a 6 on a scale of 1 to 10.

If I could push the results of a qry into a table variable using a
Select Into From (maketable query) then I might like table variables a
lot more.

-Aaron

[quoted text, click to view]

aaron.kempf NO[at]SPAM gmail.com
12/19/2006 1:40:00 PM

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (microsoft)

sql server (microsoft) : What's better for performance...