Hi Jamal:I tried using Transactions in various manners with no performance improvement. In fact, they always timed out on the Txn.Complete() statement but things were updated anyway. Then I moved the Batch update up one level so it did only a few hundred updates at a time and did the tbl.AcceptChanges() to clear the table between each but no improvement. Finally I just flat out moved the Tbl.update( xxx ) into the individual row update logic so there was one update per each of the 5,000 rows as they were updated.
No improvement nor loss of performance at any significant level.So it appears it just takes about 8 to 10 minuts to update the 5,000 rows using the Update( tbl ) or update( row ) or whatever with Sql Server. My Table is over 4,000 bytes and only about 800 bytes are being updated but they all need to be passed if I use the TableAdapter, and I think, the DataAdapter method to update the Table. I'll try something else next using the SqlCommand or whatever so I can just select the columns necessary and then use the update sql statement or something like that, I'm not sure about the DataAdapter and, or the Sql Command and CommandBuilder and the like but will do reading, sigh, on that too. Man, I'm tired of the mess of Data Structures and all the nebulus documentation and piecemeal approaches requiring tons of guesswork to working with it. Back in the old days working with IBM it was VSAM and IMS DB/DC which was straight forward and extremely well documented unlike the MS stuff, sigh, Thanks Jamal. I'll post up if I get something that works well. Perhaps I'll need to switch DataBases but that will be a major, major ReWrite since everything uses DataSets, StronglyTyped and the SqlClient Provider model.
Rick Farmington Mich. USA----- Original Message ----- From: "Richard Thomas" <rthomas@xxxxxxxxxxx>
To: <programmingblind@xxxxxxxxxxxxx> Sent: Friday, January 04, 2008 1:44 PM Subject: Re: SqlServer DataBase Update Performance Problem
Hi Jamal:I'm a little confused, as usual. There is the CommandBuilder, the DataSet with it's TableAdapters, the DataAdapter and DataReaders and Cursors all diferent methods of accessing and updating the DataBase not to mention a few diferent methods of processing Transactions. Then there is the automatic promotion of transactions to something that makes things slow and the fact that the t-sql commands are actually modified and set up for you by the MS stuff if you use almost any of the above mentioned methods. I read something about using a transaction in a certain way to improve performance, I will reread it until I get it, and will wrap the batch update in one just for kicks and post up if it does anything significant, likely tomorrow as I'm getting pretty burned out this evening, er afternoon, been up since 4:00. Perhaps I'll need to dig into some kind of Sql Performance monitor software or, better yet perhaps, switch to SqLite or the new MS Embedded DB but using SqlClint throughout all my apps makes that sound like a real headache! Oh well, it is what it is, I'm done for today, see you on the flip-side and thanks.Rick Farmington Mich. USA----- Original Message ----- From: "Jamal Mazrui" <empower@xxxxxxxxx>To: <programmingblind@xxxxxxxxxxxxx> Sent: Saturday, January 05, 2008 10:44 AM Subject: Re: SqlServer DataBase Update Performance ProblemYour 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__________View the list's information and change your settings at //www.freelists.org/list/programmingblind
__________View the list's information and change your settings at //www.freelists.org/list/programmingblind
