Bad Performance In C# Project

[aculfa]

I've just finished a C# project that processes a file and inserts data to
database.

I used "typed dataset" and filled tables in this dataset during file
process. After processing, data sent to DB by using DataAdapter.Update.

First few files are done very fast, but later on it slows down.

For performance, I tried to do everything in memory. But I should mention
that after a file process there may be a total data at about 50000rows in
memory and destruction of these tables are left to Garbage Collector.

Is this a bad way? If so, what should I do to increase performance.

Note: For first 5 files, it's twice as fast as Delphi does, but after 100
files it's twice as slow.

 

[Miha Markic [MVP C#]]

Hi aculfa,

The fastest way is to do the processing on the server.
But anyway, what exactly is slow?

 

[aculfa]

This is somehow like a bulk insert project.

6 clients get files to their local from file server, process them and
inserts data to DB.

Instead reading and updating database, I thought to process all at once, and
then to update each table to DB.

I think after a while GC starts to clear "typed datasets" from memory, which
belong to previous files. And the program slows down.

Here's a results I saw:
1st file: Completed in 21 seconds
2nd file: Completed in 25 seconds
3rd file: Completed in 30 seconds
4th file: Completed in 41 seconds
5th file: Completed in 50 seconds
6th file: Completed in 1min 40sec seconds

and the other are in the 2-3 minute range.

My opinion is that the destruction of typed dataset's in the memory takes
long, and between 5th and 6th steps GC starts collecting.

If so, is there a faster way that I can get rid of older datasets and begin
the latter file with clean memory?
If not, why does the program slows down.

(Files are similar, each at similar size, and produces about 10000 rows of
data.)

Hi aculfa,

The fastest way is to do the processing on the server.
But anyway, what exactly is slow?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I've just finished a C# project that processes a file and inserts data to
database.

I used "typed dataset" and filled tables in this dataset during file
process. After processing, data sent to DB by using DataAdapter.Update.

First few files are done very fast, but later on it slows down.

For performance, I tried to do everything in memory. But I should mention
that after a file process there may be a total data at about 50000rows in
memory and destruction of these tables are left to Garbage Collector.

Is this a bad way? If so, what should I do to increase performance.

Note: For first 5 files, it's twice as fast as Delphi does, but after 100
files it's twice as slow.

 

[Miha Markic [MVP C#]]

Hi,

I see.
Try invoking GC.Collect; GC.WaitForPendingFinalizers after each update (when
there is no reference to dataset anymore)- it will force garbage collector
to do its work.
Just for test.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

This is somehow like a bulk insert project.

6 clients get files to their local from file server, process them and
inserts data to DB.

Instead reading and updating database, I thought to process all at once, and
then to update each table to DB.

I think after a while GC starts to clear "typed datasets" from memory, which
belong to previous files. And the program slows down.

Here's a results I saw:
1st file: Completed in 21 seconds
2nd file: Completed in 25 seconds
3rd file: Completed in 30 seconds
4th file: Completed in 41 seconds
5th file: Completed in 50 seconds
6th file: Completed in 1min 40sec seconds

and the other are in the 2-3 minute range.

My opinion is that the destruction of typed dataset's in the memory takes
long, and between 5th and 6th steps GC starts collecting.

If so, is there a faster way that I can get rid of older datasets and begin
the latter file with clean memory?
If not, why does the program slows down.

(Files are similar, each at similar size, and produces about 10000 rows of
data.)

Hi aculfa,

The fastest way is to do the processing on the server.
But anyway, what exactly is slow?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I've just finished a C# project that processes a file and inserts data to
database.

I used "typed dataset" and filled tables in this dataset during file
process. After processing, data sent to DB by using DataAdapter.Update.

First few files are done very fast, but later on it slows down.

For performance, I tried to do everything in memory. But I should mention
that after a file process there may be a total data at about 50000rows in
memory and destruction of these tables are left to Garbage Collector.

Is this a bad way? If so, what should I do to increase performance.

Note: For first 5 files, it's twice as fast as Delphi does, but after 100
files it's twice as slow.

 

[Jeff Davis]

I would also be sure you're not adding rows to a single DataTable as this
process goes on. An example of this bad workflow might be that the first
file adds 1,000 rows to the DataTable, then those rows are sent and are
marked as not having any pending changes. The next file adds another 1,000
rows to the same DataTable, then those rows are sent to the DB because
they're the only ones that have changes pending. Those rows are marked as
not having any pending changes, and the process continues. If you're
following this process the GC routines won't be able to clean up data for
the old rows because you're still using them. A similar problem would occur
if each file was loaded as its own DataTable in a single DataSet. Make sure
each file loads into its own DataTable in its own DataSet so you aren't
inadvertently holding references to things you no longer need.


Jeff Davis

This is somehow like a bulk insert project.

6 clients get files to their local from file server, process them and
inserts data to DB.

Instead reading and updating database, I thought to process all at once, and
then to update each table to DB.

I think after a while GC starts to clear "typed datasets" from memory, which
belong to previous files. And the program slows down.

Here's a results I saw:
1st file: Completed in 21 seconds
2nd file: Completed in 25 seconds
3rd file: Completed in 30 seconds
4th file: Completed in 41 seconds
5th file: Completed in 50 seconds
6th file: Completed in 1min 40sec seconds

and the other are in the 2-3 minute range.

My opinion is that the destruction of typed dataset's in the memory takes
long, and between 5th and 6th steps GC starts collecting.

If so, is there a faster way that I can get rid of older datasets and begin
the latter file with clean memory?
If not, why does the program slows down.

(Files are similar, each at similar size, and produces about 10000 rows of
data.)

Hi aculfa,

The fastest way is to do the processing on the server.
But anyway, what exactly is slow?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I've just finished a C# project that processes a file and inserts data to
database.

I used "typed dataset" and filled tables in this dataset during file
process. After processing, data sent to DB by using DataAdapter.Update.

First few files are done very fast, but later on it slows down.

For performance, I tried to do everything in memory. But I should mention
that after a file process there may be a total data at about 50000rows in
memory and destruction of these tables are left to Garbage Collector.

Is this a bad way? If so, what should I do to increase performance.

Note: For first 5 files, it's twice as fast as Delphi does, but after 100
files it's twice as slow.

 

[aculfa]

Hi Davis and Markic,

Thanks for your interest to the topic..

Jeff the process is not going like the way you mentioned.

For each file, the prepared "typed datasets" (each including empty
DataTables) are instantiated.
Be sure about clean dataset for the new file.

I tested the project for about 400 files again yesterday and I came to
another point.

What I saw is, DataAdapter.Update( ) sent 440 thousand rows to one table in
620 seconds (10min) and sent 350 thousand rows to another table in 56
thousand seconds (15 hours). The only difference is second table includes 2
triggers.

Can 2 triggers slow down the process this much?
Note: Database is truncated before program starts.

I think by the time data in DB grows, and triggers take longer time to query
or insert row to other tables. Below is a graph that represents our
performance by the time. First I thought the big fall happened because of GC
but the problem seems to be in another place!!! GC shouldn't slow down the
process this much, what do you think about the problem?



I'll test both programs (C# - Delphi) without any triggers to test whether
it is a client problem or DB problem.

I would also be sure you're not adding rows to a single DataTable as this
process goes on. An example of this bad workflow might be that the first
file adds 1,000 rows to the DataTable, then those rows are sent and are
marked as not having any pending changes. The next file adds another 1,000
rows to the same DataTable, then those rows are sent to the DB because
they're the only ones that have changes pending. Those rows are marked as
not having any pending changes, and the process continues. If you're
following this process the GC routines won't be able to clean up data for
the old rows because you're still using them. A similar problem would occur
if each file was loaded as its own DataTable in a single DataSet. Make sure
each file loads into its own DataTable in its own DataSet so you aren't
inadvertently holding references to things you no longer need.


Jeff Davis

This is somehow like a bulk insert project.

6 clients get files to their local from file server, process them and
inserts data to DB.

Instead reading and updating database, I thought to process all at once, and
then to update each table to DB.

I think after a while GC starts to clear "typed datasets" from memory, which
belong to previous files. And the program slows down.

Here's a results I saw:
1st file: Completed in 21 seconds
2nd file: Completed in 25 seconds
3rd file: Completed in 30 seconds
4th file: Completed in 41 seconds
5th file: Completed in 50 seconds
6th file: Completed in 1min 40sec seconds

and the other are in the 2-3 minute range.

My opinion is that the destruction of typed dataset's in the memory takes
long, and between 5th and 6th steps GC starts collecting.

If so, is there a faster way that I can get rid of older datasets and begin
the latter file with clean memory?
If not, why does the program slows down.

(Files are similar, each at similar size, and produces about 10000 rows of
data.)

Hi aculfa,

The fastest way is to do the processing on the server.
But anyway, what exactly is slow?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I've just finished a C# project that processes a file and inserts

data
to

database.

I used "typed dataset" and filled tables in this dataset during file
process. After processing, data sent to DB by using DataAdapter.Update.

First few files are done very fast, but later on it slows down.

For performance, I tried to do everything in memory. But I should mention
that after a file process there may be a total data at about

50000rows
in

memory and destruction of these tables are left to Garbage Collector.

Is this a bad way? If so, what should I do to increase performance.

Note: For first 5 files, it's twice as fast as Delphi does, but

after
100

files it's twice as slow.

 

[Miha Markic [MVP C#]]

Hi,

Hi Davis and Markic,

Thanks for your interest to the topic..

Jeff the process is not going like the way you mentioned.

For each file, the prepared "typed datasets" (each including empty
DataTables) are instantiated.
Be sure about clean dataset for the new file.

I tested the project for about 400 files again yesterday and I came to
another point.

What I saw is, DataAdapter.Update( ) sent 440 thousand rows to one table in
620 seconds (10min) and sent 350 thousand rows to another table in 56
thousand seconds (15 hours). The only difference is second table includes 2
triggers.

Can 2 triggers slow down the process this much?
Note: Database is truncated before program starts.

I think by the time data in DB grows, and triggers take longer time to query
or insert row to other tables. Below is a graph that represents our
performance by the time. First I thought the big fall happened because of GC
but the problem seems to be in another place!!! GC shouldn't slow down the
process this much, what do you think about the problem?

Yes, it is probably on the database. Besides trigers, also indexes can slow
down the database (among other factors).

I'll test both programs (C# - Delphi) without any triggers to test whether
it is a client problem or DB problem.

Sounds reasonable.

 

[Kevin Yu]

trigger will slow down the process. I see that in my projects.

Kevin

Hi Davis and Markic,

Thanks for your interest to the topic..

Jeff the process is not going like the way you mentioned.

For each file, the prepared "typed datasets" (each including empty
DataTables) are instantiated.
Be sure about clean dataset for the new file.

I tested the project for about 400 files again yesterday and I came to
another point.

What I saw is, DataAdapter.Update( ) sent 440 thousand rows to one table in
620 seconds (10min) and sent 350 thousand rows to another table in 56
thousand seconds (15 hours). The only difference is second table includes 2
triggers.

Can 2 triggers slow down the process this much?
Note: Database is truncated before program starts.

I think by the time data in DB grows, and triggers take longer time to query
or insert row to other tables. Below is a graph that represents our
performance by the time. First I thought the big fall happened because of GC
but the problem seems to be in another place!!! GC shouldn't slow down the
process this much, what do you think about the problem?



I'll test both programs (C# - Delphi) without any triggers to test whether
it is a client problem or DB problem.

I would also be sure you're not adding rows to a single DataTable as this
process goes on. An example of this bad workflow might be that the first
file adds 1,000 rows to the DataTable, then those rows are sent and are
marked as not having any pending changes. The next file adds another 1,000
rows to the same DataTable, then those rows are sent to the DB because
they're the only ones that have changes pending. Those rows are marked as
not having any pending changes, and the process continues. If you're
following this process the GC routines won't be able to clean up data for
the old rows because you're still using them. A similar problem would occur
if each file was loaded as its own DataTable in a single DataSet. Make sure
each file loads into its own DataTable in its own DataSet so you aren't
inadvertently holding references to things you no longer need.


Jeff Davis


once,
and

rows

of

data.)




"Miha Markic [MVP C#]" <miha at rthand com>, iletide þunu yazdý
Hi aculfa,

The fastest way is to do the processing on the server.
But anyway, what exactly is slow?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I've just finished a C# project that processes a file and inserts data
to
database.

I used "typed dataset" and filled tables in this dataset during file
process. After processing, data sent to DB by using DataAdapter.Update.

First few files are done very fast, but later on it slows down.

For performance, I tried to do everything in memory. But I should
mention
that after a file process there may be a total data at about 50000rows
in
memory and destruction of these tables are left to Garbage Collector.

Is this a bad way? If so, what should I do to increase performance.

Note: For first 5 files, it's twice as fast as Delphi does, but after
100
files it's twice as slow.