INSERT perfomance and Indexes 
Author Message
 INSERT perfomance and Indexes

I have a table with about 500,000 records which gets hit
for a lot of inserts by the client.  The performance is
crucial so I've been trying to reduce the time taken for
these inserts and found something odd.

Since I want to maximise transaction throughput I dropped
ALL the indexes on the table and just treated it as a big
heap.  An INSERT on the table was taking about 1 second
which is way too slow.  What I've now found is that if I
create an index on the table (clustered or non-clustered)
the performance is way better, down to about 1/20th second.

I don't understand this.  Surely with no indexes the
server only has to add a record on the data page?  With an
index defined the server also has to add a record on the
index page and update the row pointer.

The presence of an index can only possibly mean more work
for the server.  Can anyone explain to me why this is
faster?

Many thanks.



Sat, 28 Feb 2004 19:45:05 GMT
 INSERT perfomance and Indexes

My guess ( and it would be a guess since I never used a table of any size
without an index) would be that in a heap the new row is inserted in the
first available spot on any page. SQL Server may have to scan the pages to
find the first spot to insert it and that may take a short while. If there
is an index it can use the index to determine where to place it.  Is this
table ever used for selects? How can you live with no indexes?  A smart
amount of indexes should not severely impact your inserts with adequate
hardware setup. Make sure your logs are not I/O constrained.

--
Andrew J. Kelly,  SQL Server MVP
TargitInteractive

I proudly support the PASS SQL Server user community and its upcoming user
event,
 PASS 2001 North America. For details, visit <http://www.sqlpass.org>
www.sqlpass.org.)


Quote:
> I have a table with about 500,000 records which gets hit
> for a lot of inserts by the client.  The performance is
> crucial so I've been trying to reduce the time taken for
> these inserts and found something odd.

> Since I want to maximise transaction throughput I dropped
> ALL the indexes on the table and just treated it as a big
> heap.  An INSERT on the table was taking about 1 second
> which is way too slow.  What I've now found is that if I
> create an index on the table (clustered or non-clustered)
> the performance is way better, down to about 1/20th second.

> I don't understand this.  Surely with no indexes the
> server only has to add a record on the data page?  With an
> index defined the server also has to add a record on the
> index page and update the row pointer.

> The presence of an index can only possibly mean more work
> for the server.  Can anyone explain to me why this is
> faster?

> Many thanks.



Sat, 28 Feb 2004 20:10:32 GMT
 INSERT perfomance and Indexes
Thanks for the suggestion.  It's possible, although even
with a non-clustered index surely it still has to figure
out where to put the new row?  The index will tell the
server where rows are, not where they're not!

I'm not I/O bound in the log because the performance is
fine once I add the index.  The reason I don't have any
indexes is quite simple - I don't make any SELECTs.  I DTS
the data out each night into a separate reporting database
(which is indexed up to the hilt).  In this database my
goal is quite simple.  Get in, write the record, and get
out as fast as possible.  That's why I figured no indexes
would be my best bet.

Quote:
>-----Original Message-----
>My guess ( and it would be a guess since I never used a
table of any size
>without an index) would be that in a heap the new row is
inserted in the
>first available spot on any page. SQL Server may have to
scan the pages to
>find the first spot to insert it and that may take a

short while. If there
Quote:
>is an index it can use the index to determine where to
place it.  Is this
>table ever used for selects? How can you live with no
indexes?  A smart
>amount of indexes should not severely impact your inserts
with adequate
>hardware setup. Make sure your logs are not I/O
constrained.

>--
>Andrew J. Kelly,  SQL Server MVP
>TargitInteractive

>I proudly support the PASS SQL Server user community and
its upcoming user
>event,
> PASS 2001 North America. For details, visit

<http://www.sqlpass.org>

- Show quoted text -

Quote:
>www.sqlpass.org.)



>> I have a table with about 500,000 records which gets hit
>> for a lot of inserts by the client.  The performance is
>> crucial so I've been trying to reduce the time taken for
>> these inserts and found something odd.

>> Since I want to maximise transaction throughput I
dropped
>> ALL the indexes on the table and just treated it as a
big
>> heap.  An INSERT on the table was taking about 1 second
>> which is way too slow.  What I've now found is that if I
>> create an index on the table (clustered or non-
clustered)
>> the performance is way better, down to about 1/20th
second.

>> I don't understand this.  Surely with no indexes the
>> server only has to add a record on the data page?  With
an
>> index defined the server also has to add a record on the
>> index page and update the row pointer.

>> The presence of an index can only possibly mean more
work
>> for the server.  Can anyone explain to me why this is
>> faster?

>> Many thanks.

>.



Sat, 28 Feb 2004 20:34:54 GMT
 INSERT perfomance and Indexes
To *really* maximize insert performance, you should create a clustered index
with an appropriate fill factor (probably LOW)  that will reduce page splits
as much as possible - In other words, a clustered index with lots of room
that is in a random order relatoive to insert order.  Then, you need to
maintain that fill factor by rebuilding the index periodically.

Don't know why the non-clustered index helps -That's weird, unless those
pointers somehow get used like the CI row id's get used...


Quote:
> Thanks for the suggestion.  It's possible, although even
> with a non-clustered index surely it still has to figure
> out where to put the new row?  The index will tell the
> server where rows are, not where they're not!

> I'm not I/O bound in the log because the performance is
> fine once I add the index.  The reason I don't have any
> indexes is quite simple - I don't make any SELECTs.  I DTS
> the data out each night into a separate reporting database
> (which is indexed up to the hilt).  In this database my
> goal is quite simple.  Get in, write the record, and get
> out as fast as possible.  That's why I figured no indexes
> would be my best bet.

> >-----Original Message-----
> >My guess ( and it would be a guess since I never used a
> table of any size
> >without an index) would be that in a heap the new row is
> inserted in the
> >first available spot on any page. SQL Server may have to
> scan the pages to
> >find the first spot to insert it and that may take a
> short while. If there
> >is an index it can use the index to determine where to
> place it.  Is this
> >table ever used for selects? How can you live with no
> indexes?  A smart
> >amount of indexes should not severely impact your inserts
> with adequate
> >hardware setup. Make sure your logs are not I/O
> constrained.

> >--
> >Andrew J. Kelly,  SQL Server MVP
> >TargitInteractive

> >I proudly support the PASS SQL Server user community and
> its upcoming user
> >event,
> > PASS 2001 North America. For details, visit
> <http://www.sqlpass.org>
> >www.sqlpass.org.)



> >> I have a table with about 500,000 records which gets hit
> >> for a lot of inserts by the client.  The performance is
> >> crucial so I've been trying to reduce the time taken for
> >> these inserts and found something odd.

> >> Since I want to maximise transaction throughput I
> dropped
> >> ALL the indexes on the table and just treated it as a
> big
> >> heap.  An INSERT on the table was taking about 1 second
> >> which is way too slow.  What I've now found is that if I
> >> create an index on the table (clustered or non-
> clustered)
> >> the performance is way better, down to about 1/20th
> second.

> >> I don't understand this.  Surely with no indexes the
> >> server only has to add a record on the data page?  With
> an
> >> index defined the server also has to add a record on the
> >> index page and update the row pointer.

> >> The presence of an index can only possibly mean more
> work
> >> for the server.  Can anyone explain to me why this is
> >> faster?

> >> Many thanks.

> >.



Sun, 29 Feb 2004 08:13:28 GMT
 INSERT perfomance and Indexes
That's a good suggestion.  I can see why this solution gives increased
performance since the server can use the ci to determine where to add the
new record on the leaf page and with a low fill factor and a random
distribution of INSERTs I am going to get very few page splits.  Ok, happy
there.

What I can't see is why the addition of a nci gives such a big performance
gain (comparable with adding a ci).  As I understand it the server shouldn't
even consult the nci when making the INSERT, except to update the nci after
the INSERT has been made (which is *extra* work).


Quote:
> To *really* maximize insert performance, you should create a clustered
index
> with an appropriate fill factor (probably LOW)  that will reduce page
splits
> as much as possible - In other words, a clustered index with lots of room
> that is in a random order relatoive to insert order.  Then, you need to
> maintain that fill factor by rebuilding the index periodically.

> Don't know why the non-clustered index helps -That's weird, unless those
> pointers somehow get used like the CI row id's get used...



> > Thanks for the suggestion.  It's possible, although even
> > with a non-clustered index surely it still has to figure
> > out where to put the new row?  The index will tell the
> > server where rows are, not where they're not!

> > I'm not I/O bound in the log because the performance is
> > fine once I add the index.  The reason I don't have any
> > indexes is quite simple - I don't make any SELECTs.  I DTS
> > the data out each night into a separate reporting database
> > (which is indexed up to the hilt).  In this database my
> > goal is quite simple.  Get in, write the record, and get
> > out as fast as possible.  That's why I figured no indexes
> > would be my best bet.

> > >-----Original Message-----
> > >My guess ( and it would be a guess since I never used a
> > table of any size
> > >without an index) would be that in a heap the new row is
> > inserted in the
> > >first available spot on any page. SQL Server may have to
> > scan the pages to
> > >find the first spot to insert it and that may take a
> > short while. If there
> > >is an index it can use the index to determine where to
> > place it.  Is this
> > >table ever used for selects? How can you live with no
> > indexes?  A smart
> > >amount of indexes should not severely impact your inserts
> > with adequate
> > >hardware setup. Make sure your logs are not I/O
> > constrained.

> > >--
> > >Andrew J. Kelly,  SQL Server MVP
> > >TargitInteractive

> > >I proudly support the PASS SQL Server user community and
> > its upcoming user
> > >event,
> > > PASS 2001 North America. For details, visit
> > <http://www.sqlpass.org>
> > >www.sqlpass.org.)



> > >> I have a table with about 500,000 records which gets hit
> > >> for a lot of inserts by the client.  The performance is
> > >> crucial so I've been trying to reduce the time taken for
> > >> these inserts and found something odd.

> > >> Since I want to maximise transaction throughput I
> > dropped
> > >> ALL the indexes on the table and just treated it as a
> > big
> > >> heap.  An INSERT on the table was taking about 1 second
> > >> which is way too slow.  What I've now found is that if I
> > >> create an index on the table (clustered or non-
> > clustered)
> > >> the performance is way better, down to about 1/20th
> > second.

> > >> I don't understand this.  Surely with no indexes the
> > >> server only has to add a record on the data page?  With
> > an
> > >> index defined the server also has to add a record on the
> > >> index page and update the row pointer.

> > >> The presence of an index can only possibly mean more
> > work
> > >> for the server.  Can anyone explain to me why this is
> > >> faster?

> > >> Many thanks.

> > >.



Sun, 29 Feb 2004 17:27:45 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SS7 full text indexing perfomance question

2. Tools for Index Perfomance Analysis

3. Perfomance for multiple inserts

4. perfomance problem index not used

5. Bitmap index and perfomance

6. Problems with query perfomance using the filtered indexes

7. drop and recreate index vs large insert with index

8. inserting, index and no index - speed

9. inserting, index and no index - speed

10. How to insert data from un-indexed tabel to indexed table

11. Help! Slow SQL Server perfomance, migrated from Access

12. PERFOMANCE LOST ON DUAL PROCESSORS


 
Powered by phpBB® Forum Software