Re: SqlServer DataBase Update Performance Problem
- From: "Will Pearson" <will@xxxxxxxxxxxxx>
- To: <programmingblind@xxxxxxxxxxxxx>
- Date: Mon, 7 Jan 2008 07:33:08 -0000
Hi Rick,
I don't use databases, and so can't help with the specifics of your situation;
however, the first step in optimisation is usually to use a profiler to see
what parts of your code are taking a long time to run. This helps in
establishing where the problem is. Performance problems are often not where
you think they are, and so profiling is a really valuable step in optimisation.
There are quite a lot of profilers out there. Some cost money but I think
there are some free ones as well. The one I use is the one that is built into
Visual Studio 2005's Team Developer and Team Suite editions.
You might also find Rico Mariani's blog useful. Rico is now the Cheif
Architect for Visual Studio but before that he was the performance guru for
Microsoft's Developer Division. You can find Rico's blog at:
http://blogs.msdn.com/ricom
Will
----- Original Message -----
From: Richard Thomas
To: programmingblind@xxxxxxxxxxxxx
Sent: Friday, January 04, 2008 12:14 PM
Subject: SqlServer DataBase Update Performance Problem
Hi Gang:
I have a Sql Server DB. I use the DataSet approach, strongly typed, to do
the following for about 5,000 rows.
Load MyDataTable using MyTableAdapter.
Iterate the DataSet, DataTable using the
For Each MyRow in MyDataTable
update about 25 columns for each MyRow.xxx
Next MyRow
When all rows updated, do Batch Push To DB:
MyDataAdapter.Update( MyDataTable )
The above is from memory but I think the syntax is ok, anyway, it takes over
10 minuts to run the batch push for only 5,000 rows.
First, that is not acceptable to me so I would like to know the fastest
method of updating my DataBase.
Should I use a CommandBuilder, I read some bad things about that, a
DataAdapter, ditto or what? A cursor?
Do you have anything on my problem as updating only a few thousand records
should be almost a matter of a few seconds at most shouldn't it?
It would be a split second for a flat or indexed file.
Rick Farmington Mich. USA
Other related posts:
