Resolved, Re: SqlServer DataBase Update Performance Problem
- From: "Richard Thomas" <rthomas@xxxxxxxxxxx>
- To: <programmingblind@xxxxxxxxxxxxx>
- Date: Thu, 10 Jan 2008 12:49:19 -0500
Hi Guys:
Here is the final code:
Sorry it took so long but i wrote a program to auto generate all the
necessary code from the CreateTable DDL to avoid keying mistakes and have a
reusable program for future use.
This solution takes about 5 to 10 seconds to push the 5,000 plus rows
instead of over 10 minuts.
There are another 30 Decimal columns which will be added to the below but
they will have no significant performance impact so I left them out for
brevity.
Public Function UpdateMetrix( ByVal TickersTable As
StockFDataSet.StocksDataTable) As Integer
Dim RowUpdated As Integer = 0
Dim TotalRowsUpdated As Integer = 0
Dim MySqlCommand As SqlCommand
MySqlCommand = New System.Data.SqlClient.SqlCommand
MySqlCommand.CommandType = System.Data.CommandType.Text
Dim DbConnection As New SqlConnection()
DbConnection = Me._connection
DbConnection.Open()
MySqlCommand.Connection = DbConnection
MySqlCommand.CommandText = _
"UPDATE [Stocks] " & _
"SET " & _
"[LastTradeDate] = @LastTradeDate, " & _
"[LastTradePrice] = @LastTradePrice, " & _
"[ProjectedTotReturnThisYear] = @ProjectedTotReturnThisYear, " & _
"[ProjectedTotReturnNextYear] = @ProjectedTotReturnNextYear " & _
"WHERE [pkStocks] = @pkStocks;"
Dim Parm As SqlParameter
Parm = New SqlParameter("@pkStocks", SqlDbType.Int)
MySqlCommand.Parameters.Add( Parm )
Parm = New SqlParameter("@LastTradeDate", SqlDbType.VarChar)
MySqlCommand.Parameters.Add( Parm )
Parm = New SqlParameter("@LastTradePrice", SqlDbType.Decimal)
Parm.Precision = 15
Parm.Scale = 2
MySqlCommand.Parameters.Add( Parm )
Parm = New SqlParameter("@ProjectedTotReturnThisYear", SqlDbType.Decimal)
Parm.Precision = 15
Parm.Scale = 2
MySqlCommand.Parameters.Add( Parm )
Parm = New SqlParameter("@ProjectedTotReturnNextYear", SqlDbType.Decimal)
Parm.Precision = 15
Parm.Scale = 2
MySqlCommand.Parameters.Add( Parm )
Dim Ticker As StockFDataSet.StocksRow
For Each Ticker in TickersTable.Rows
MySqlCommand.Parameters( "@pkStocks").Value = Ticker.pkStocks
MySqlCommand.Parameters( "@LastTradeDate").Value = Ticker.LastTradeDate
MySqlCommand.Parameters( "@LastTradePrice").Value = Ticker.LastTradePrice
MySqlCommand.Parameters( "@ProjectedTotReturnThisYear").Value =
Ticker.ProjectedTotReturnThisYear
MySqlCommand.Parameters( "@ProjectedTotReturnNextYear").Value =
Ticker.ProjectedTotReturnNextYear
Try
RowUpdated = MySqlCommand.ExecuteNonQuery
TotalRowsUpdated = TotalRowsUpdated + RowUpdated
Catch ex As Exception
Logger.WriteLine( "Catch Triggered")
Logger.WriteLine( ex.ToString())
Exit For
End Try
Next Ticker
Logger.WriteLine( "All Done, Total Updates " & TotalRowsUpdated)
DbConnection.Close()
Return TotalRowsUpdated
End Function
I call the above function from my Business Class that handles gathering,
parsing and formatting the data for updating the Metrix fields for the
Stocks Table.
So that's it, phew! If you have any ideas to make it even faster let me
know.
Rick Farmington Mich. USA
----- Original Message -----
From: "Chris Westbrook" <westbc@xxxxxxxxx>
To: <programmingblind@xxxxxxxxxxxxx>
Sent: Wednesday, January 09, 2008 5:39 PM
Subject: Re: SqlServer DataBase Update Performance Problem
Make sure you're using transactions so if an update fails the changes get
roled back. Post what you have when you're done and I'll see if I can
improve it. I do this stuff every day for my work.
----- Original Message -----
From: "Richard Thomas" <rthomas@xxxxxxxxxxx>
To: <programmingblind@xxxxxxxxxxxxx>
Sent: Monday, January 07, 2008 11:44 AM
Subject: Re: SqlServer DataBase Update Performance Problem
Hi Chris and Jamal:
Well, I stripped the large request down. Then set up one useing the
SqlCommand and SqlParameter objects with only a couple of fields.
Ran a test against the 5,000 rows and it updated in about 5 seconds, much
better than 10 minuts the standard Adapter.Update( MyTable ) took!
The AutoGenerated code did allot of checking of values against original
values for the where clause. It also did other checking not necessary
for my application.
In My TableAdapter Partial Class Extension I also opened a connection
manually and held it open until the batch was processed. I may go back
and set pooling to true and try the DataSet's TableAdapter version of
the Batch Update again to see if that was the problem or if it was just
the AutoGenerated overhead.
When I have a good grip of what caused the performance hit I'll post up
the results and sample code that I used to get around it.
It is either the pooling value forcing too many open and closes or the
AutoGenerated overhead, or the 30 plus columns with the 4,000 bytes per
row either way I'll keep testing and taking notes, figure it out and
post the answer.
Rick Farmington Mich. USA
----- Original Message -----
From: "Chris Westbrook" <westbc@xxxxxxxxx>
To: <programmingblind@xxxxxxxxxxxxx>
Sent: Sunday, January 06, 2008 1:56 PM
Subject: Re: SqlServer DataBase Update Performance Problem
What are you trying to do and maybe we can help. Something tells me you
shouldn't really be updating 5000 rows at a time. I think you might
ahve better luck using data readers to get the info and an sqlcommand to
do all hte updates inside a transaction. YOu really should use
transactions, since I'm sure you don't want only some of the updates to
happen. Use a separate connection for the datareader and hte update
command as you can't perform updates on a connection when a datareader
is open.
----- Original Message -----
From: "Richard Thomas" <rthomas@xxxxxxxxxxx>
To: <programmingblind@xxxxxxxxxxxxx>
Sent: Saturday, January 05, 2008 12:28 PM
Subject: Re: SqlServer DataBase Update Performance Problem
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 Problem
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
__________
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
__________
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
Other related posts:
