Re: SqlServer DataBase Update Performance Problem
- From: Jamal Mazrui <empower@xxxxxxxxx>
- To: programmingblind@xxxxxxxxxxxxx
- Date: Sat, 5 Jan 2008 10:44:40 -0500 (EST)
Your approach seems right, and the execution time is puzzling indeed.
With SQLite, I know that performance is much slower if one does not
surround a set of updates with "begin transaction" and "end transaction"
constructs -- otherwise, each update is a seperate transaction. I
wonder if a similar thing might be happening here.
Jamal
On Fri, 4 Jan 2008,
Richard Thomas wrote:
> Date: Fri, 4 Jan 2008 07:14:55 -0500
> From: Richard Thomas <rthomas@xxxxxxxxxxx>
> Reply-To: programmingblind@xxxxxxxxxxxxx
> To: programmingblind@xxxxxxxxxxxxx
> 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
__________
View the list's information and change your settings at
//www.freelists.org/list/programmingblind
Other related posts:
