Very Computer

by **Ramesh bab** » Wed, 10 Jul 2002 20:48:39

hi,
I am using temporary table in my stored procedure.
For improving performance, i have created Index.

Is there is any difference between "Creating Index Before
inserting data" and "Creating Index after inserting data"?

If so, Which method of creation will improve performance?

Any help?

Thanks,
Ramesh

by **Andrew J. Kell** » Wed, 10 Jul 2002 21:05:06

Ramesh,

Inserts are generally faster if there are no indexes on the table but it
also takes time to create the index itself. It really depends on what the
table looks like, how many rows etc. If your only talking a few rows then
you probably won't notice much of a difference either way. I would try both
ways using your unique requirements and see which one performs better.

--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.

Quote:> hi,
> I am using temporary table in my stored procedure.
> For improving performance, i have created Index.

> Is there is any difference between "Creating Index Before
> inserting data" and "Creating Index after inserting data"?

> If so, Which method of creation will improve performance?

> Any help?

> Thanks,
> Ramesh

by **Narayana Vyas Kondredd** » Wed, 10 Jul 2002 21:11:18

What's the amount of data you are inserting, in terms of number of rows?
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/

Quote:> hi,
> I am using temporary table in my stored procedure.
> For improving performance, i have created Index.

> Is there is any difference between "Creating Index Before
> inserting data" and "Creating Index after inserting data"?

> If so, Which method of creation will improve performance?

> Any help?

> Thanks,
> Ramesh

by **Alan Z. Schar** » Thu, 11 Jul 2002 01:59:12

There is also the question whether you are populating the entire table with
an INSERT INTO statement or SELECT INTO.

The latter will be much faster with many rows since it bypasses logging.

If your circumstances allow you to populate most or all of the temp table
with a large number of rows via SELECT INTO before building the index, this
may tip the balance. Would be worth testing.

Alan

Quote:> hi,
> I am using temporary table in my stored procedure.
> For improving performance, i have created Index.

> Is there is any difference between "Creating Index Before
> inserting data" and "Creating Index after inserting data"?

> If so, Which method of creation will improve performance?

> Any help?

> Thanks,
> Ramesh

Very Computer

Creating Index in Temporary table

Creating Index in Temporary table

Creating Index in Temporary table

Creating Index in Temporary table

Creating Index in Temporary table