Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP Job
This sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>sql

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx
.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>

INSERT TRUNCATION

Hi,
Thanks for all responces.
I am using insert to populate a table and I need to truncate a 50 [char]
length field to a 20 [Char] lenth field in the insert.
Any recomemdations?
Again thanks
George
You can explicitly CAST as desired. For example:
INSERT INTO MyTable (MyShorterColumn)
SELECT CAST(MyLongerColumn AS char(20))
Hope this helps.
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thanks for all responces.
> I am using insert to populate a table and I need to truncate a 50 [char]
> length field to a 20 [Char] lenth field in the insert.
> Any recomemdations?
> Again thanks
> George
>
|||I think that is the syntax I am looking for, will try it and get back to
you.
Thanks.
George
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>
|||PERFECT! Mutiple truncations in one line and it works perfect.
THANKS
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>
|||I'm glad it helped you out.
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:%23slSqvUoEHA.3252@.TK2MSFTNGP14.phx.gbl...
> PERFECT! Mutiple truncations in one line and it works perfect.
> THANKS
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
>
sql

INSERT TRUNCATION

Hi,
Thanks for all responces.
I am using insert to populate a table and I need to truncate a 50 [char]
length field to a 20 [Char] lenth field in the insert.
Any recomemdations?
Again thanks
GeorgeYou can explicitly CAST as desired. For example:
INSERT INTO MyTable (MyShorterColumn)
SELECT CAST(MyLongerColumn AS char(20))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thanks for all responces.
> I am using insert to populate a table and I need to truncate a 50 [char]
> length field to a 20 [Char] lenth field in the insert.
> Any recomemdations?
> Again thanks
> George
>|||I think that is the syntax I am looking for, will try it and get back to
you.
Thanks.
George
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Thanks for all responces.
>> I am using insert to populate a table and I need to truncate a 50 [char]
>> length field to a 20 [Char] lenth field in the insert.
>> Any recomemdations?
>> Again thanks
>> George
>|||PERFECT! Mutiple truncations in one line and it works perfect.
THANKS
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Thanks for all responces.
>> I am using insert to populate a table and I need to truncate a 50 [char]
>> length field to a 20 [Char] lenth field in the insert.
>> Any recomemdations?
>> Again thanks
>> George
>|||I'm glad it helped you out.
--
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:%23slSqvUoEHA.3252@.TK2MSFTNGP14.phx.gbl...
> PERFECT! Mutiple truncations in one line and it works perfect.
> THANKS
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
>> You can explicitly CAST as desired. For example:
>> INSERT INTO MyTable (MyShorterColumn)
>> SELECT CAST(MyLongerColumn AS char(20))
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "george collins" <george@.nospan.com> wrote in message
>> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Thanks for all responces.
>> I am using insert to populate a table and I need to truncate a 50 [char]
>> length field to a 20 [Char] lenth field in the insert.
>> Any recomemdations?
>> Again thanks
>> George
>>
>