I have a number of remote sql servers from which I collect data. Said data i
s
inserted into #temp tables, such as for example:
CREATE TABLE dbo.#tmp
(
x int not null primary key
, y int not null
)
INSERT INTO dbo.#tmp (x, y)
SELECT x, y FROM server.db.dbo.table
For sync'ing this data with local tables it is adventageous to have an index
on y, x:
CREATE NONCLUSTERED INDEX IX_tmp
ON dbo.#tmp (y, x)
Does anyone see any reason to create that index before or after inserting
the records? I.e. is scenario (A) or (B) below better?
(A) 1. Create temp table; 2. Define index; 3. Insert records
(B) 1. Create temp table; 2. Insert records; 3. Create index
My gut tells me that it's a wash, and keeping the index definition with the
table (in code) is better for maintainability, but there's probably no
performance benefit either way. Or maybe there is a performance benefit to
one that I can't think of?
Comments?
Thanks - KenKH wrote:
> I have a number of remote sql servers from which I collect data. Said
> data is inserted into #temp tables, such as for example:
> CREATE TABLE dbo.#tmp
> (
> x int not null primary key
> , y int not null
> )
> INSERT INTO dbo.#tmp (x, y)
> SELECT x, y FROM server.db.dbo.table
> For sync'ing this data with local tables it is adventageous to have
Temp table usage in stored procedures can be a source of recompilation.
To avoid recompiles (which are costly), you should try to avoid
interleaving DML and DDL statements related to temp tables. Therefore,
you are better off defining all your temp tables up front and creating
indexes on them before inserting or otherwise manipulating data in the
tables.
David Gugick
Imceda Software
www.imceda.com|||If you are doing Bulk Insert of a large number of records at once...
Then drop and recreate the indices. I quote from Books OnLine, From the
Bulk Insert Entry.
"If nonclustered indexes are also present on the table, drop these before
copying data into the table. It is generally faster to bulk copy data into a
table without nonclustered indexes, and then to re-create the nonclustered
indexes, rather than bulk copy data into a table with the nonclustered
indexes in place."
The only exception is when you have a clustered Index on teh table, AND you
have the luxury of pre-sorting the data in in the same Order as they will be
in the Clusterd Index (Therefore Inserting the records in Clustered Index
Order). In THis special case, leave the CLustered Index on the table during
the Insert.
"David Gugick" wrote:
> KH wrote:
> Temp table usage in stored procedures can be a source of recompilation.
> To avoid recompiles (which are costly), you should try to avoid
> interleaving DML and DDL statements related to temp tables. Therefore,
> you are better off defining all your temp tables up front and creating
> indexes on them before inserting or otherwise manipulating data in the
> tables.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
