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

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[i]

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!
|
*************************************************
[quoted text, click to view]

Are you sure that you are not using the Fill in a loop as you did while
using the AddNew method in ADO?

We cannot see that

By the way, this instruction you can better leave out.
[quoted text, click to view]

The commandbuilder builds its command dynamicly everytime the adapter is
used for an update.

Cor

"Harsha" <sriharsha.galve@gmail.com> schreef in bericht
news:d9967c60-ada5-4942-ad6c-5d497fcc1b0e@e10g2000prf.googlegroups.com...
[quoted text, click to view]