Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
skweeky
Starting Member
9 Posts |
 Posted - 2002-07-01 : 05:31:45
|
| hi all from france, i have two versions of same stored poc : one is using temp tables and another is using table variable.when i execute proc with # table, it's take 35s and 2 recompilationswhen i execute proc with @ table, it's take 22s and no recompilationswell, it's good for table variable, but when i launch profiler to watch locks on TempDB database, there is more locks for table variables than for temp tables !!!! (ratio 2x)how can you explain that !???thanks for all,nico |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-07-01 : 06:52:51
|
| I believe that @table variables are a special form of table and utilise memory....and not disk access....search for @table in this site...it has come up before...or look-up BOL.2nd factor is to take account of is...data caching....does run #1 affect the measurement of run #2? using the following commands between tests resets cache to ensure independance of results.dbcc dropcleanbuffersdbcc freeproccacheHTH |
 |
|
|
skweeky
Starting Member
9 Posts |
Posted - 2002-07-01 : 08:19:15
|
| all was already reseted in cache by your two dbcc commands ! |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-07-01 : 08:29:00
|
| Can you post your Sp code here. possible reasons could be if you use a Select into without creating the table first , the SP is recompiled on its each execution.-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
skweeky
Starting Member
9 Posts |
Posted - 2002-07-01 : 10:23:09
|
| hi !i make no Select Into, only Create Table #temp for one, and declare @temp table for another !in the query, there are several Union, is this can be the problem !???thks for help!nico |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-07-02 : 04:04:14
|
| Unions are resource crunching . but , i dont the difference in execution speed is coz of that. firstly, table variables are in memory so they are faster then temp tables. i would still like to look at your sp to get a better picture.-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-07-02 : 04:15:09
|
| Table variables DO perform disk I/O in TempDB, although you won't see the table in profiler. If you load enough data into a table variable then it "could" possible write a temp table in TempDB.DavidM"SQL-3 is an abomination.."Edited by - byrmol on 07/02/2002 04:19:07 |
|
|
|
|
|
|
|
|
Table Variables and TempDB