Creating Index in Temporary table

Creating Index in Temporary table

Post 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

 
 
 

Creating Index in Temporary table

Post 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


 
 
 

Creating Index in Temporary table

Post 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

 
 
 

Creating Index in Temporary table

Post 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

 
 
 

1. Create Index to Temporary Table

Please help

I am trying to create an index in a temporary table, but when I try to
access the index it keep giving me the following error.
Msg 308, Level 16, State 1
Index '#deano_list_ind1' on table '#deano_list' (specified in the FROM
clause) does not exist.

the code is as follow

select amsclaim.deano

into #deano_list

from    amsclaim(INDEX = rxfill_payor)
where   (amsclaim.rxfill between  'JAN 1 1996' and  'JAN 1 1996') and
        (amsclaim.payor = '0YA') and
        (amsclaim.nosub = '1')

create index #deano_list_ind1 on #deano_list(deano)

select * from #deano_list (index = #deano_list_ind1)

Please help

Tomas S Polanco

2. ODBC NT server client takes 30 minutes to connect o Oracle 7.3.2 server when on isolated network???

3. Creating Index for Temporary tables

4. Larry Ellison

5. Problem creating Index for Temporary Table

6. Boolean casting in 7.3 -> changed?

7. Create Index for temporary table

8. help Creating Index on Temporary Tables

9. Temporary Table: Implicitely created index not shown in \d

10. [Help] Temporary Table: Implicitely created index not

11. Temporary Table: Implicitely created index not

12. [Help] Temporary Table: Implicitely created index not shown in \d i