Insert performance/nvarchar

hello,
i wrote a performance test for sequential inserts with ado.net on a P4 2GHz
512 Meg Ram machine
and got the following scores:
insert 10000 ints 1:30 mins
insert 10000 reals 1:20 mins
inserting 10000 nvarchars
first 10000: 1:30
second 10000: 4:11
third 10000: 6:50
fourth 10000: 9:30
fifth 10000: 12:12
sixth 10000: 15:00
seventh 10000 18:20
so the times gets worse and worse.
i would expect, that the convergate but they don't
Is this normal?
If yes we will have problems, because we expect a couple of millions entries
in this
table where this strings are stored.
all tables for the performance test have the same stucture and indices
except of
the datatype which is tested, which is
id
value
Can you give me a hint how to speed this?
thanks mike
Do you have your databases auto-growing during these tests, or did you set
the files to a large enough size before the tests to ensure that they
wouldn't grow?
Do you have the columns indexed? Are the inserts causing page splits?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> hello,
> i wrote a performance test for sequential inserts with ado.net on a P4
2GHz
> 512 Meg Ram machine
> and got the following scores:
> insert 10000 ints 1:30 mins
> insert 10000 reals 1:20 mins
> inserting 10000 nvarchars
> first 10000: 1:30
> second 10000: 4:11
> third 10000: 6:50
> fourth 10000: 9:30
> fifth 10000: 12:12
> sixth 10000: 15:00
> seventh 10000 18:20
> so the times gets worse and worse.
> i would expect, that the convergate but they don't
> Is this normal?
> If yes we will have problems, because we expect a couple of millions
entries
> in this
> table where this strings are stored.
> all tables for the performance test have the same stucture and indices
> except of
> the datatype which is tested, which is
> id
> value
> Can you give me a hint how to speed this?
> thanks mike
|||Hello Adam,
yes its auto-growing
yes columns are indexed
most inserts are NOT causing page splits
thanks mike
"Adam Machanic" wrote:

> Do you have your databases auto-growing during these tests, or did you set
> the files to a large enough size before the tests to ensure that they
> wouldn't grow?
> Do you have the columns indexed? Are the inserts causing page splits?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> 2GHz
> entries
>
>
|||You'll get more consistent results if you grow the file first...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...[vbcol=seagreen]
> Hello Adam,
> yes its auto-growing
> yes columns are indexed
> most inserts are NOT causing page splits
> thanks mike
>
> "Adam Machanic" wrote:
set[vbcol=seagreen]
|||hello adam,
nope,
i dropped the old database, created a new one with fixed size
600 meg (for data and tranlog).
The behaviour is still the same.
The insert times are growing endless.
greetings mike
"Adam Machanic" wrote:

> You'll get more consistent results if you grow the file first...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> set
>
>
|||Can you post the table definitions, including constraints and indexes?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...[vbcol=seagreen]
> hello adam,
> nope,
> i dropped the old database, created a new one with fixed size
> 600 meg (for data and tranlog).
> The behaviour is still the same.
> The insert times are growing endless.
> greetings mike
>
> "Adam Machanic" wrote:
you[vbcol=seagreen]
they[vbcol=seagreen]
splits?[vbcol=seagreen]
message[vbcol=seagreen]
a P4[vbcol=seagreen]
millions[vbcol=seagreen]
indices[vbcol=seagreen]
|||Hello Adam,
here comes the table
create table LogStringTable
(
ID int identity (1,1) not null,
stringValue nvarchar(400) not null,
attributeTypeId int not null,
logItemId int not null
constraint FKATIhasStringValues
foreign key ( attributeTypeId )
references LogAttributeType (Id),
constraint FKItemHasStringvalues
foreign key (LogItemId )
references LogItem ( Id )
) on primary
there are four indices
primary key index on Id (clustered)
and an the other columns (not unique)
thank you mike
"Adam Machanic" wrote:

> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> you
> they
> splits?
> message
> a P4
> millions
> indices
>
>
|||Hello Adam,
I dropped all indices and tried again
-> nothing principaly changed.
The times are shorter, but they are still growing endless,
with each 10000 insert.
When I have 100.000 entries in that table than the performance is reduce to
about
15 inserts/second compared with 100 inserts/second when starting with a
blank table.
And the performance goes down and down.
Greets mike
"Adam Machanic" wrote:

> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> you
> they
> splits?
> message
> a P4
> millions
> indices
>
>
|||hello adam,
i know now, that it is not a problem if the sql server.
it has to do with ado.net.
currently i don't know what it is, but now I inserted 10.000 nvarchars with
the
query analyzer and it lasts about 5 seconds
regardless how many records are in the table.
so i have to look into the ado.net stuff.
thank you for your help
I'll let you know what is is, when i know it.
thank you very much
greets michael
"Adam Machanic" wrote:

> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> you
> they
> splits?
> message
> a P4
> millions
> indices
>
>
|||Hello Adam,
I got it.
There is an option at the data adapter called
refresh the dataset
This was set to true.
I set it to false and now my world is perpendicular again.
The insert times for 10.000 stings are now about 5-7 seconds.
Unfortunatly, I even didn't use a dataset, so this option is useless even
when set to true.
I think this is worthy a microsoft call.
thank you for your help.
mike.
"Adam Machanic" wrote:

> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> you
> they
> splits?
> message
> a P4
> millions
> indices
>
>