September 26, 2006 at 5:52 pm
I know Select * has a performance impact and its better to use Select Column1, column2...
But is this also true for Select * from #TempTables. ?
September 26, 2006 at 10:00 pm
For temp table there is no index so we can use select *
September 27, 2006 at 1:09 am
From a best practice point of view you should never use select * - unless I guess you want the entire table, but even there it could lead to code breaking.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 27, 2006 at 3:17 am
Since when did temp table stop accepting indexes. This is a real table. The fact that it resides in tempdb doesn't change it's nature. You can add constraints, indexes (clustered or not) and defaults all you want. Therefore best pratices still apply.
September 27, 2006 at 9:51 pm
do u thing can we create index for #temptable .
September 28, 2006 at 2:03 am
The problem with SELECT * isnt so much the query performance per se but the bandwidth requirement returning the data. In most scenarios, you are generating far more network traffic than you need. (I agree theres also more i/o on the server but the impact of that is less than on the network [I'm a developer, not a dba!]. Only return the fields that you actually need, preferably via parameterized stored procedures. This gives greater security and greater visibility as to what is happening.
September 28, 2006 at 4:52 am
create table #demo (ID int not null primary key clustered, SomeVal int not null)
create unique index IX_Demo on #demo (SomeVal).
Assuming I didn't screw up the syntax this will work. You can check for yourself in tempdb and you'll find those 3 objects.
September 28, 2006 at 10:35 am
There are 3 impacts of using select *
1. If the table is indexed, the index won't fully cover the query so extra IO to access the underlying table to get the full row of data will be required
2. Bandwidth will be used needlessly. This is particularly noticable in network bandwidth with large result sets, but also has an impact on memory usage on the server even if the result set isn't being passed back to the client (as is often the case with selecting from a temporary table used to store intermediate result sets). The increased memory footprint required by using select * can adversely affect system-wide scalability and performance.
3. The applications using the query can break if they receive unexpected information, which they will get if the underlying table changes.
It's just lazy coding to do select *. Don't do it, whatever kind of table you're querying.
September 28, 2006 at 1:51 pm
Thanks all.. I read this in some forum somewhere but didbt pay much attention to it at that time. This person tried to create 2 differrent StoredProc and each had a #temptable with the same name. Eg #Results. and had the same name for the indexes.
the 1st stocPrc rnus ok, but When executing 2nd StorProc SQL gave error that index alrady exists. #temptable is only visible to the window thats calliig it but is this also true for indexes for #TempTables.
September 28, 2006 at 1:56 pm
Should be. The only way I can see this conflit happen is if the 2nd sp is calling by the first one. Since they share the same scope, the 2nd sp can see everything created in the main sp. So that would cause the error.
September 28, 2006 at 2:21 pm
thanks. the reason is that i am new to this cmopany as dba. and the performance of 1 database has been slow for some weeks. I have to tell the senior developers to change their quiers from Select * from #TempTable to indivual column names. Just hoping that would give some performance gain. But i could also say that its good programming practise.
September 28, 2006 at 2:27 pm
I wouldn't expect a huge gain (unless GBs of data are sent over the network for no good reason every hour).
Looks like you need to work on the indexing / perf tuning part of the application.
But it's definitly a yes on the good practice part.
September 28, 2006 at 2:58 pm
yes there are hugh rows exchanged > 300,000 mostly and hugh #temptables joined at the end this select * from #temptable.
i will also look into indexing the #temptables.
Thanks all for ur help.
September 29, 2006 at 1:49 am
I think you have another bad practice there - no housekeeping. Always explicitly drop a temp table as soon as you have finished with it. Remember that # tables are global to the connection so your 2nd sp is still seeing the one created by the first.
## tables are global to everyone while the creating session lasts. Not sure what happens if the creating connection ends but someone else has the ## open - i guess it lives until the last user stops accessing it. I've never had occassion to use a ## !
September 29, 2006 at 2:12 pm
“This person tried to create 2 differrent StoredProc and each had a #temptable with the same name. Eg #Results. and had the same name for the indexes.
the 1st stocPrc rnus ok, but When executing 2nd StorProc SQL gave error that index alrady exists. #temptable is only visible to the window thats calliig it but is this also true for indexes for #TempTables. “
What you’ve described here can happen, to an extent. The scenario you described can happen if the temporary tables were using Primary Keys for indexes. Consider this, when you create a temporary table, SQL Server creates the table in the TempDB and gives that table a unique name that only your session has access to.
CREATE TABLE #tmpTable (Col1 int NOT NULL, Col2 int)
SELECT Name
FROM TempDB..SysObjects
WHERE Xtype = 'U'
Results:
#tmpTable_______________________________________________________________00000000001F
Now Open up a new QA session (a new connection) and run the same query.
CREATE TABLE #tmpTable (Col1 int NOT NULL, Col2 int)
SELECT Name
FROM TempDB..SysObjects
WHERE Xtype = 'U'
New Results:
#tmpTable_______________________________________________________________000000000021
#tmpTable_______________________________________________________________00000000001F
All is well right? SQL Server creates the unique name to satisfy the Unique index on sysobjects on Name, UID. Now let’s add Primary Keys to both temp tables and see what happens.
From the first session:
ALTER TABLE #tmpTable ADD CONSTRAINT PK_tmpTable_Col1 PRIMARY KEY CLUSTERED (Col1)
SELECT Name
FROM TempDB..SysObjects
WHERE Xtype = 'U' or Xtype = 'PK'
Results:
#tmpTable________________________________________________________________000000000021
PK_tmpTable_Col1
#tmpTable________________________________________________________________00000000001F
OK, we can now see the Primary Key we just created in TempDB..SysObjects. Now, let’s try to add the same Primary Key with the same name on the second temp table.
From the second session:
ALTER TABLE #tmpTable ADD CONSTRAINT PK_tmpTable_Col1 PRIMARY KEY CLUSTERED (Col1)
SELECT Name
FROM TempDB..SysObjects
WHERE Xtype = 'U' or Xtype = 'PK'
Results:
Server: Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table '#tmpTable'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
What happened?? Well, since SQL Server only creates unique names for the temp table names and not all objects associated with a temporary table, a second instance of a PK object will fail because the TempDB..Sysobjects table already has an entry with that UID and Name.
So what does all of this mean? Well, it means that due to the way SQL Server stores Primary Keys in the SysObjects table rather than the SysIndexes table, you must be careful when defining Primary Keys on temporary tables. You can either let SQL Server define the PK name, or use Indexes only – not Primary Keys.
It is also important to note that this can happen when the PK is defined at table-creation time also. In this case, the second table will be created, but without a PK.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply