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
