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 slow operation

hi ,
I have large no. of insert operations in my application .
Can I disable indexes somehow & make it fast . Or Can anyone suggest what
all best possiblities for the same .
Thanks
ARR
Aju
You cannot disable indexes. You can only drop them and re-create after
inserting.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>
|||Hi,
Drop the indexes apart from Clustered index and do the insert. You can
recreate the non clusterd index after the insert. This will speed
up the process. If you have some auditing triggers you can disable them
either.
Thanks
Hari
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>
|||For high-volume inserts, consider using a bulk load method such as BULK
INSERT, BCP or DTS. These are much faster than standard Transact-SQL
INSERTs. You can also speed things up by batching multiple INSERT
statements in a single transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>

insert slow operation

hi ,
I have large no. of insert operations in my application .
Can I disable indexes somehow & make it fast . Or Can anyone suggest what
all best possiblities for the same .
Thanks
ARRAju
You cannot disable indexes. You can only drop them and re-create after
inserting.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||Hi,
Drop the indexes apart from Clustered index and do the insert. You can
recreate the non clusterd index after the insert. This will speed
up the process. If you have some auditing triggers you can disable them
either.
Thanks
Hari
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||For high-volume inserts, consider using a bulk load method such as BULK
INSERT, BCP or DTS. These are much faster than standard Transact-SQL
INSERTs. You can also speed things up by batching multiple INSERT
statements in a single transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>

insert slow operation

hi ,
I have large no. of insert operations in my application .
Can I disable indexes somehow & make it fast . Or Can anyone suggest what
all best possiblities for the same .
Thanks
ARRAju
You cannot disable indexes. You can only drop them and re-create after
inserting.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||Hi,
Drop the indexes apart from Clustered index and do the insert. You can
recreate the non clusterd index after the insert. This will speed
up the process. If you have some auditing triggers you can disable them
either.
Thanks
Hari
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||For high-volume inserts, consider using a bulk load method such as BULK
INSERT, BCP or DTS. These are much faster than standard Transact-SQL
INSERTs. You can also speed things up by batching multiple INSERT
statements in a single transaction.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>

Insert query timing out

This questions pertains to the administration aspect.
I have a table with 7 million records. We have defined about 5 indexes
according to our
reporting needs.The queries are performing satisfactorily.But offlate,we
have observed
that our insert queries are timing out. we are trying to insert a record
into the table
three times with a time out of 15 seconds everytime.the insert query is
timing out even
after three times. we are trying to find if there is anything wrong with the
table
structure.The table has about 30 columns and is designed properly.
I have run dbcc show contig on the table and observed that there is external
fragmentation with the table according to the statistics.
Please let me if my observation is wrong based on the statistics.
DBCC SHOWCONTIG scanning 'xxxx' table...
Table: 'xxxx' (1767677345); index ID: 1, database ID: 24 TABLE
level scan performed.
- Pages Scanned........................: 665043
- Extents Scanned.......................: 84073
- Extent Switches.......................: 181138
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 45.89% [83131:181139]
- Logical Scan Fragmentation ..............: 14.47%
- Extent Scan Fragmentation ...............: 24.48%
- Avg. Bytes Free per Page................: 1130.5
- Avg. Page Density (full)................: 86.03%
there is no fill factor defined on the clustered index.
i have run dbcc reindex with no fill factor but it did not any good w.r.t
insert query
time out.
Do we need to change the fill factor from o to 80?
another question,why are we having external fragmentation on the table?
and what can be done to stop external fragmentation on the table?
Does backup play any role in the table fragmentation?
please advise us on what can be done to avoid insert query time out?
every month we see about 4 million records in this table.Hi
You don't give the DDL for the table and indexes which would be very useful
information to have when answering this question. You also don't say what
updates/deletes occur on this table, or where you are inserting the new data.
Have you checked for blocking?
John
"Deepak" wrote:
> This questions pertains to the administration aspect.
> I have a table with 7 million records. We have defined about 5 indexes
> according to our
> reporting needs.The queries are performing satisfactorily.But offlate,we
> have observed
> that our insert queries are timing out. we are trying to insert a record
> into the table
> three times with a time out of 15 seconds everytime.the insert query is
> timing out even
> after three times. we are trying to find if there is anything wrong with the
> table
> structure.The table has about 30 columns and is designed properly.
> I have run dbcc show contig on the table and observed that there is external
> fragmentation with the table according to the statistics.
> Please let me if my observation is wrong based on the statistics.
> DBCC SHOWCONTIG scanning 'xxxx' table...
> Table: 'xxxx' (1767677345); index ID: 1, database ID: 24 TABLE
> level scan performed.
> - Pages Scanned........................: 665043
> - Extents Scanned.......................: 84073
> - Extent Switches.......................: 181138
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 45.89% [83131:181139]
> - Logical Scan Fragmentation ..............: 14.47%
> - Extent Scan Fragmentation ...............: 24.48%
> - Avg. Bytes Free per Page................: 1130.5
> - Avg. Page Density (full)................: 86.03%
> there is no fill factor defined on the clustered index.
> i have run dbcc reindex with no fill factor but it did not any good w.r.t
> insert query
> time out.
> Do we need to change the fill factor from o to 80?
> another question,why are we having external fragmentation on the table?
> and what can be done to stop external fragmentation on the table?
> Does backup play any role in the table fragmentation?
> please advise us on what can be done to avoid insert query time out?
> every month we see about 4 million records in this table.
>|||Hello John
Thanks for replying. I have checked for blocking and there are no blocks.I
have run sql profiler and have not observed any locks during the time period
on this table .
There are only updates and inserts. there are no delete operations on this
table.
Updates are very minimum. inserts happen every other second during the peak
times.we have about 4 million transactions per month on this table. the issue
is only on inserting . do you believe that there is external fragmentation on
this table?
what is your take on changing the fill factor for the clustered index? this
is a normal table with 30 or more columns. the issue is only with inserts and
not with select queries.
Please advise us.
"John Bell" wrote:
> Hi
> You don't give the DDL for the table and indexes which would be very useful
> information to have when answering this question. You also don't say what
> updates/deletes occur on this table, or where you are inserting the new data.
> Have you checked for blocking?
> John
>
> "Deepak" wrote:
> > This questions pertains to the administration aspect.
> > I have a table with 7 million records. We have defined about 5 indexes
> > according to our
> >
> > reporting needs.The queries are performing satisfactorily.But offlate,we
> > have observed
> >
> > that our insert queries are timing out. we are trying to insert a record
> > into the table
> >
> > three times with a time out of 15 seconds everytime.the insert query is
> > timing out even
> >
> > after three times. we are trying to find if there is anything wrong with the
> > table
> >
> > structure.The table has about 30 columns and is designed properly.
> > I have run dbcc show contig on the table and observed that there is external
> >
> > fragmentation with the table according to the statistics.
> > Please let me if my observation is wrong based on the statistics.
> > DBCC SHOWCONTIG scanning 'xxxx' table...
> > Table: 'xxxx' (1767677345); index ID: 1, database ID: 24 TABLE
> > level scan performed.
> > - Pages Scanned........................: 665043
> > - Extents Scanned.......................: 84073
> > - Extent Switches.......................: 181138
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 45.89% [83131:181139]
> > - Logical Scan Fragmentation ..............: 14.47%
> > - Extent Scan Fragmentation ...............: 24.48%
> > - Avg. Bytes Free per Page................: 1130.5
> > - Avg. Page Density (full)................: 86.03%
> >
> > there is no fill factor defined on the clustered index.
> > i have run dbcc reindex with no fill factor but it did not any good w.r.t
> > insert query
> >
> > time out.
> > Do we need to change the fill factor from o to 80?
> > another question,why are we having external fragmentation on the table?
> > and what can be done to stop external fragmentation on the table?
> > Does backup play any role in the table fragmentation?
> > please advise us on what can be done to avoid insert query time out?
> > every month we see about 4 million records in this table.
> >|||"Deepak" <Deepak@.discussions.microsoft.com> wrote in message
news:28B684B3-DE3F-44BF-AEF5-39512DEAEBBD@.microsoft.com...
> Hello John
> Thanks for replying. I have checked for blocking and there are no blocks.I
> have run sql profiler and have not observed any locks during the time
> period
> on this table .
> There are only updates and inserts. there are no delete operations on this
> table.
> Updates are very minimum. inserts happen every other second during the
> peak
> times.we have about 4 million transactions per month on this table. the
> issue
> is only on inserting . do you believe that there is external fragmentation
> on
> this table?
> what is your take on changing the fill factor for the clustered index?
> this
> is a normal table with 30 or more columns. the issue is only with inserts
> and
> not with select queries.
What do you mean by "timeout". SQL Server doesn't time-out queries, client
programs do. So what's the client program and how long is the timeout?
What else is going on? Are you very, very sure there's no blocking. This
sounds very much like a blocking problem.
The measures you propose might decrease transaction times slightly, but it's
unlikely that they will resolve your timeout issue.
David|||Hello
The client program is a vb component. the time out is 15 seconds. I try the
insert query three times . I am positive that there is no blocking.
thanks
Deepak
"David Browne" wrote:
> "Deepak" <Deepak@.discussions.microsoft.com> wrote in message
> news:28B684B3-DE3F-44BF-AEF5-39512DEAEBBD@.microsoft.com...
> > Hello John
> > Thanks for replying. I have checked for blocking and there are no blocks.I
> > have run sql profiler and have not observed any locks during the time
> > period
> > on this table .
> > There are only updates and inserts. there are no delete operations on this
> > table.
> > Updates are very minimum. inserts happen every other second during the
> > peak
> > times.we have about 4 million transactions per month on this table. the
> > issue
> > is only on inserting . do you believe that there is external fragmentation
> > on
> > this table?
> > what is your take on changing the fill factor for the clustered index?
> > this
> > is a normal table with 30 or more columns. the issue is only with inserts
> > and
> > not with select queries.
> What do you mean by "timeout". SQL Server doesn't time-out queries, client
> programs do. So what's the client program and how long is the timeout?
> What else is going on? Are you very, very sure there's no blocking. This
> sounds very much like a blocking problem.
> The measures you propose might decrease transaction times slightly, but it's
> unlikely that they will resolve your timeout issue.
> David
>
>|||Hi Deepak
I am surprised you say there is no locking or blocking, the process that
inserts should take out locks. Have you looked at lock escalation, lock
timeouts and deadlocks in SQL profiler? You may also want to look at the
errors/warnings and log at statement level to show more information about any
triggers being executed. Also look at the output from sp_who2 when the
process is running. You may want to try sp_blocker_pss80 see
http://support.microsoft.com/kb/271509.
I don't think fragmentation is the issue, only one in 4 extents are not
contiguous and you still have the problem after doing a re-index. You don't
say when the DBCC SHOWCONTIG information was taken! You may want to try
dropping the indexes to see what effect that has.
Have you checked perfmon information regarding the number of read/writes and
their duration and current disc queue lengths? You should also look at CPU
and memory usage to see if there are bottle necks there. Check out articles
on http://www.sql-server-performance.com/ on how to detect hardware
bottlenecks.
Are your log and data files on different spindles? Also see if you can
separate your indexes onto their own spindles as well!
If the source of these inserts is a data file you may want to review if you
can delay them until a quiet period and use BULK INSERT or BCP to populate
the information. You may also wish to look at partitioning the table.
Make sure that you are not continually expanding the data and log files, if
you expansion is too often you may wish to increase how much they expands.
Also if you continually shrink the data/log files there may be fragmentation
of the files on disc, check with the windows defragmentation program to see
if this is the case.
Check that your transactions are not too long. Make sure you are not
dependent on user input for them to complete. DBCC OPENTRAN will show open
transactions.
John
"Deepak" wrote:
> Hello
> The client program is a vb component. the time out is 15 seconds. I try the
> insert query three times . I am positive that there is no blocking.
> thanks
> Deepak
> "David Browne" wrote:
> >
> > "Deepak" <Deepak@.discussions.microsoft.com> wrote in message
> > news:28B684B3-DE3F-44BF-AEF5-39512DEAEBBD@.microsoft.com...
> > > Hello John
> > > Thanks for replying. I have checked for blocking and there are no blocks.I
> > > have run sql profiler and have not observed any locks during the time
> > > period
> > > on this table .
> > > There are only updates and inserts. there are no delete operations on this
> > > table.
> > > Updates are very minimum. inserts happen every other second during the
> > > peak
> > > times.we have about 4 million transactions per month on this table. the
> > > issue
> > > is only on inserting . do you believe that there is external fragmentation
> > > on
> > > this table?
> > > what is your take on changing the fill factor for the clustered index?
> > > this
> > > is a normal table with 30 or more columns. the issue is only with inserts
> > > and
> > > not with select queries.
> >
> > What do you mean by "timeout". SQL Server doesn't time-out queries, client
> > programs do. So what's the client program and how long is the timeout?
> > What else is going on? Are you very, very sure there's no blocking. This
> > sounds very much like a blocking problem.
> >
> > The measures you propose might decrease transaction times slightly, but it's
> > unlikely that they will resolve your timeout issue.
> >
> > David
> >
> >
> >