A couple of ways of solving this:
1. Create a stored procedure that has a header like
CREATE PROCEDURE UpdateRecords
(
@sessionID uniqueidentifier --assuming ASP.NET session
, @ids xml -- could also be varchar, etc.
, @reportingDate datetime
)
Then let the server handle the records. There are two options here I can
think of immediately:
A. If XML, use the XML like a table
B. If a string, rip it and use a temp table
Assuming a temp table:
UPDATE Table
SET sessionID = @sessionID
, reportingDate = @reportingDate
FROM Table t
JOIN #tempTable tt
ON t.ID = tt.ID
The main hit here is setting up the temp table or the XML as a table. The
update, provided the table is tuned properly (if large) will happen in a
fraction of a second.
2. Create your own SQL Statement with the IDs to update (my VB is rusty, so
correct as needed)
Dim builder As New StringBuilder()
builder.Append("UPDATE Table SET sessionID = '")
builder.Append(sessionID)
builder.Append("', reportingDate = '")
builder.Append(DateTime.Now)
builder.Append( "' WHERE ID in (")
'Loop here to append ids
For i as Int = 0 to ds.Count
DataRow r = ds.Rows
If i <> 0 Then
builder.Append(',')
End If
builder.Append(r.Columns["id"]
Next i
builder.Append(')')
Then run the command. If you have a variety of sessionIDs or reporting
dates, you will have to write individual statements or, if there are clumps
of dates/ids, you will have to create individual clumps of ids in a
statement.
Both of these assume a single session and a single date.
Here are some questions you need to answer as you drive through this:
1. How many records are you updating at one time?
This makes a difference, as a DataSet is not really designed for massive
amounts of updates.
2. How many records are you showing at a time?
This may or may not make a difference.
--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA
Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss
or just read it:
http://gregorybeamer.spaces.live.com/
*************************************************
| Think outside the box!
|
*************************************************
Harsha said:
Hi Experts,
I am having new problem again.
This is regarding SqlDataAdapter fill method performance. Even though
its discussed several times my problem is little different.
I had migrated one VB6 application to .Net 2.0. In VB6 application
they were using RecordSet.
Like,
----------------------
Dim rs As New ADODB.Recordset
cnn.Open GetDataProvider()
rs.Open cSQLUpdateTimeSeries, cnn, adOpenDynamic, adLockPessimistic
----------------------
Later, RecordSet get updated, Like
----------------------
For k = LBound(data, 1) To UBound(data, 1)
rs.AddNew
rs![sessionID] = session
.
.
.
rs![ReportingDate] = Dates(LBound(Dates, 1), k)
.
.
Next
rs.Update
End If
Next
rs.Close
cnn.Close
----------------------
When I migrated the code to ASP.Net 2.0, I used SqlDataAdapter.
----------------------
adap = New SqlDataAdapter(cSQLUpdateRequest, cnn)
Dim cmdBuilder As SqlCommandBuilder = New
SqlCommandBuilder(adap)
adap.InsertCommand = cmdBuilder.GetInsertCommand()
adap.Fill(dt)
----------------------
Here in between some calculation is happening and need to update in
database.
Dataset is updated like,
----------------------
For k = LBound(data, 1) To UBound(data, 1)
Dim dr As DataRow = dt.NewRow()
dr("sessionID") = session
.
.
.
dr("ReportingDate") = Dates(LBound(Dates, 1), k)
.
.
dt.Rows.Add(dr)
Next
adap.Update(dt)
----------------------
Here my problem is <b> adap.Fill(dt) </b> is extemly slow and Is there
any different approach for the above scenario to update the database
(otherthan using adapter) ?
Thank you
Sriharsha Karagodu
