We need to import lots of data into some tables. To speed
up the import, we deside to drop the indexes related to
the tables that will receive the data before the import
and then recreate the index after the import. What's the
best practice for the senarios below? When to drop/keep
indexes? What kind of indexes(clustered/non-
clustered/unique)to drop? Many thanks.
1. The import scripts do select first and then insert.
2. The import scripts do insert first and then delete.
3. The import scripts do select first and then update.It depends on what kind of indexes you have on the tables; sometimes,
indexes can help with data imports (e.g. seqential clustered indexes tend to
speed up data import). For a better answer, post DDL (CREATE TABLE
statements) for your tables. Include all indexes and constraints. Also
describe in more detail what the import will be doing. There are no best
practices; it all depends on the situation.
"Bill" <fei0405@.yahoo.com> wrote in message
news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
> We need to import lots of data into some tables. To speed
> up the import, we deside to drop the indexes related to
> the tables that will receive the data before the import
> and then recreate the index after the import. What's the
> best practice for the senarios below? When to drop/keep
> indexes? What kind of indexes(clustered/non-
> clustered/unique)to drop? Many thanks.
> 1. The import scripts do select first and then insert.
> 2. The import scripts do insert first and then delete.
> 3. The import scripts do select first and then update.
>|||Thank you very much for the reply.
eg.There is a table we will insert about 150,000 records.
Here is the scripts I got from gernerate scripts utility:
CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
[SETID] [char] (5) COLLATE Latin1_General_BIN NOT
NULL ,
[INST_PROD_ID] [char] (20) COLLATE
Latin1_General_BIN NOT NULL ,
[MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
NULL ,
[ATTRIBUTE_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_ITEM_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTRIBUTE_VALUE] [char] (254) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_DATE] [PSDATE] NULL ,
[ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
[ROW_ADDED_DTTM] [PSDATETIME] NULL ,
[ROW_ADDED_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
[ROW_LASTMANT_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[SYNCID] [int] NOT NULL ,
[SYNCDTTM] [PSDATETIME] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
[dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
[ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
But when I issue command "sp_helpindex PS_RF_ATTR_INSP
go" I got more nonclustered index names as below:
index_name index_description
index_keys
PS_RF_ATTR_INSP clustered, unique located on PRIMARY
SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
index_name index_description
index_keys
PS0RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CUST, SETID, INST_PROD_ID
PS1RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CONTACT, SETID, INST_PROD_ID
... ...
Please advise. Many thanks.
>--Original Message--
>It depends on what kind of indexes you have on the
tables; sometimes,
>indexes can help with data imports (e.g. seqential
clustered indexes tend to
>speed up data import). For a better answer, post DDL
(CREATE TABLE
>statements) for your tables. Include all indexes and
constraints. Also
>describe in more detail what the import will be doing.
There are no best
>practices; it all depends on the situation.
>
>"Bill" <fei0405@.yahoo.com> wrote in message
>news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
>> We need to import lots of data into some tables. To
speed
>> up the import, we deside to drop the indexes related to
>> the tables that will receive the data before the import
>> and then recreate the index after the import. What's the
>> best practice for the senarios below? When to drop/keep
>> indexes? What kind of indexes(clustered/non-
>> clustered/unique)to drop? Many thanks.
>> 1. The import scripts do select first and then insert.
>> 2. The import scripts do insert first and then delete.
>> 3. The import scripts do select first and then update.
>>
>
>.
>|||Bill,
I am a bit confused about those nonclustered indexes; they contain columns
that don't appear to be in the table (BO_ID_CUST, etc).
Anyway, with no knowledge of your data it is still quite difficult to answer
this question; 150,000 rows is not a very large insert and if you already
have millions of rows in the table dropping and re-creating the indexes may
take more time than the insert itself, even if there are a lot of page
splits taking place. I think you really need to run a test batch with each
method to determine what's right for you.
"Bill" <fei0405@.yahoo.com> wrote in message
news:3a9301c49f17$d664c910$a401280a@.phx.gbl...
> Thank you very much for the reply.
> eg.There is a table we will insert about 150,000 records.
> Here is the scripts I got from gernerate scripts utility:
> CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
> [SETID] [char] (5) COLLATE Latin1_General_BIN NOT
> NULL ,
> [INST_PROD_ID] [char] (20) COLLATE
> Latin1_General_BIN NOT NULL ,
> [MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
> NULL ,
> [ATTRIBUTE_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_ITEM_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTRIBUTE_VALUE] [char] (254) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_DATE] [PSDATE] NULL ,
> [ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
> [ROW_ADDED_DTTM] [PSDATETIME] NULL ,
> [ROW_ADDED_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
> [ROW_LASTMANT_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [SYNCID] [int] NOT NULL ,
> [SYNCDTTM] [PSDATETIME] NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
> [dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
> [ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> But when I issue command "sp_helpindex PS_RF_ATTR_INSP
> go" I got more nonclustered index names as below:
> index_name index_description
> index_keys
> PS_RF_ATTR_INSP clustered, unique located on PRIMARY
> SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
> index_name index_description
> index_keys
> PS0RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CUST, SETID, INST_PROD_ID
> PS1RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CONTACT, SETID, INST_PROD_ID
> ... ...
>
> Please advise. Many thanks.
