inserts become very slow after table becomes large

[Robert]

using asa9.0.2, have a table that has touched 6 million+ records, now the
inserts into it; around 10,000 records at a time have become really slow.

What should be the correct way to improve peformance of inserts on large
tables ..

obvious ones are .
1. delete data from the table that is not required
2. expand db file so their are contigous blocks available and does not cause
fragmentation
3. improve hardware, current hardware is 1 GHz dual core processor with 400
M's of RAM out of which 250 M is given to the db instance

Ran index consultant it did not ask to create any indexes on the large
table, all the queries against it are by the primary key hence thinking no
need for it.

Any ideas ? thanks

[Greg Fenton]

Post by Robert
using asa9.0.2, have a table that has touched 6 million+ records, now the
inserts into it; around 10,000 records at a time have become really slow.

Is it slow once (rebalancing of index?) or is it consistently slow?

Have you check your db and disk fragmentation?

g.f

[Glenn Paulley]

Post by Robert
using asa9.0.2, have a table that has touched 6 million+ records, now
the inserts into it; around 10,000 records at a time have become
really slow.
What should be the correct way to improve peformance of inserts on
large tables ..
obvious ones are .
1. delete data from the table that is not required

There should be little correlation between INSERT performance and table
size. Overall file system fragmentation and database page fragmentation
may play a role, but these issues are somewhat orthogonal to the size of
the table per se.

Post by Robert
2. expand db file so their are contigous blocks available and does not
cause fragmentation

This can be significant. How many file system fragments do you have for
this dbspace? (use db_extended_property, so the console log window may
suffice).

Post by Robert
3. improve hardware, current hardware is 1 GHz dual core processor
with 400 M's of RAM out of which 250 M is given to the db instance

Please give the command line with which you start the server.

Post by Robert
Ran index consultant it did not ask to create any indexes on the large
table, all the queries against it are by the primary key hence
thinking no need for it.

Indexes do not help INSERT...VALUES statements, only other DML statements
like SELECT. Additional indexes are simply additional maintenance
overhead, which must be traded-off with how the indexes can be exploited
by other statements in your application's workload.

Post by Robert
Any ideas ? thanks

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

Register now for TechWave 2006 August 6-10 in Las Vegas. New! 2-day
and 4-day options for SQL Anywhere technical training. Sessions will
focus on data management, data movement, and SQL Anywhere 10. Visit
www.sybase.com/techwave

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

[David Kerber]

In article <***@10.22.241.106>,
***@ianywhere.com says...


...

Post by Glenn Paulley
This can be significant. How many file system fragments do you have for
this dbspace? (use db_extended_property, so the console log window may
suffice).

Post by Robert
3. improve hardware, current hardware is 1 GHz dual core processor
with 400 M's of RAM out of which 250 M is given to the db instance

Please give the command line with which you start the server.

Post by Robert
Ran index consultant it did not ask to create any indexes on the large
table, all the queries against it are by the primary key hence
thinking no need for it.

I suppose if there were some triggers, the action in the triggers might
benefit from indexes.

Post by Glenn Paulley
Indexes do not help INSERT...VALUES statements, only other DML statements
like SELECT. Additional indexes are simply additional maintenance
overhead, which must be traded-off with how the indexes can be exploited
by other statements in your application's workload.

A clustered index could add even more overhead, couldn't it?

[anil k goel]

Yes; a true clustered index will indeed add more overhead.

However, in SA, the clustering of an index is only a hint and is not
enforced. The server tries to keep data clustered without incurring
additional overhead. If data is inserted in random order, then you will
likely get local clustering only.
--
-anil
Query Processing, iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Post by David Kerber
...

Post by Glenn Paulley
This can be significant. How many file system fragments do you have for
this dbspace? (use db_extended_property, so the console log window may
suffice).

Post by Robert
3. improve hardware, current hardware is 1 GHz dual core processor
with 400 M's of RAM out of which 250 M is given to the db instance

Please give the command line with which you start the server.

Post by Robert
Ran index consultant it did not ask to create any indexes on the large
table, all the queries against it are by the primary key hence
thinking no need for it.

I suppose if there were some triggers, the action in the triggers might
benefit from indexes.

Post by Glenn Paulley
Indexes do not help INSERT...VALUES statements, only other DML statements
like SELECT. Additional indexes are simply additional maintenance
overhead, which must be traded-off with how the indexes can be exploited
by other statements in your application's workload.

A clustered index could add even more overhead, couldn't it?
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

[Rob Waywell]

In addition to Glenn's questions & comments, can you define "really slow"
for us?
What was the insert rate when it was "really fast"?
What is the insert rate now that it is "really slow"?
Inserts do require updating indexes, so there is potential for blocking.
When you run sa_conn_info() do you see any blocking occuring?
How are the inserts being peformed?
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Post by Robert
using asa9.0.2, have a table that has touched 6 million+ records, now the
inserts into it; around 10,000 records at a time have become really slow.
What should be the correct way to improve peformance of inserts on large
tables ..
obvious ones are .
1. delete data from the table that is not required
2. expand db file so their are contigous blocks available and does not
cause fragmentation
3. improve hardware, current hardware is 1 GHz dual core processor with
400 M's of RAM out of which 250 M is given to the db instance
Ran index consultant it did not ask to create any indexes on the large
table, all the queries against it are by the primary key hence thinking no
need for it.
Any ideas ? thanks