Server side cursor

Hi All,

I appolozise for posting this query over here, as I could not find any other place, so am posting over here.

I ran into a strange problem, when I use server side cursor with ADO 2.7 (MDAC Refresh) I get the cursor operation conflict when I try to update the recordset or do a filter second time on the same recordset.
I donn't say that this is the problem with MDAC, as if I run the same code against different server other than mine, I donn't get this error. But if I move the code to other machine and test against my database(SQL Server 2000 with sp2) I see the same problem on there machine. Is there any thing wrong with my database. But when I run with client side cursor type I donn't see this error.

What does the SQL server (or ADO's use) use for connecting when I use server side cursor on ADO recordset?

Any kind of help is greatly appreciated. Thank you.

murali.k.maddali

There was an article recently written about server-side cursors:
http://www.sqlteam.com/item.asp?ItemID=11842

As for the specific problem you are having, I have noticed that certain features act up in ADO when specifed as a server cursor, but when specified as a client cursor it works. I would suggest you go with a client cursor if that is acceptable.


"How do you expect to beat me when I am forever?"

I initially thought of going with client side cursor, but when I ran my setup on one of our client machine, The client side cursor failed saying that "Row cannot be located for updating, some values may have been changed since it was last read." (some thing similar) error. When looking upon, I had seen that I update a record and in my second loop I update the same record again, this should not be happening, but due to wrong data I get, this must be overseen. I have read couple articles on this error but none were helpfull. But when I work with the same data on my machine that works wonderfull. So was unable to figure this out. and more over Client side cursors are much slower than the server side cursors.

Is there any other thing that I look for, Thank you.

murali.k.maddali

quote:Originally posted by royv

There was an article recently written about server-side cursors:
http://www.sqlteam.com/item.asp?ItemID=11842

As for the specific problem you are having, I have noticed that certain features act up in ADO when specifed as a server cursor, but when specified as a client cursor it works. I would suggest you go with a client cursor if that is acceptable.


"How do you expect to beat me when I am forever?"

It depends on what you are doing to say whether or not a server-side or client-side cursor is better. In your case, yes it would seem a server side cursor is better since you want to update records in the database. But if you just wanted to browse some records, it is almost always better to go with a client-side cursor, unless the amount of records are too big.


"How do you expect to beat me when I am forever?"

Thanks to All of you for your responses,

I have seen the problem I mentioned about server side cursors only on my machine, so I planned to do a quick rebuild, as I cann't afford wasting time no more on this. Any way here is my situation, and would really like to hear some suggestions reagarding this.

All I have to do is transfer data between two tables, this sounds quite simple, but I happen to be hit by performance issues. I have to check for the record in the destination table and do an insert or update on the record accordingly.

Right now I am using ADO recordset object, for this task. I cann't use update statement as I have to build the set clause, in some cases there may be more than 52 columns.

Can any one suggest me a better way of doing this? Right now it takes me more than 4 hrs to update say 20000 records, with 52 columns. This is way too long, I am running this on pIII with 256mb ram.

I have tried this with both server side and client side cursor types on the recordset. I am using adOpenForwardOnly Cursor type and adLockReadOnly lock type on source recordset. and adOpenStatic Cursor type and adLockOptimistic Lock type on destination recordset.

Is there any way I can achieve the same task from SQL queries?

Any kind of help is greatly appreciated.

Thank you,
murali.k.maddali

The last question you have asked, did you also post on sqlteam.com? I answered that question there.


"How do you expect to beat me when I am forever?"

Here is the sample code that I was talking off, I just wanted to give an overview, of what I am doing, so there might be some code missing in copy paste, but I can assure you that this works fine.

Do Until srcRs.EOF

With dstRs
If ((UBound(sIDFieldNameArr) - LBound(sIDFieldNameArr)) > 0) Then
.Filter = sFindCriteria
Else
.Find sFindCriteria, 0, adSearchForward, adBookmarkFirst
End If
End With

If dstRs.EOF Then
If dstRs.Supports(adAddNew) Then
dstRs.AddNew
For iCounter = 0 To (iColCount - 1)
With srcRs.Fields(iCounter)
dstRs(.Name).Value = Trim(.Value)
iLoop2 = iLoop2 + 1
End With
Next iCounter
Else
' update the record here
For iCounter = 0 To (iColCount - 1)
With srcRs.Fields(iCounter)
sFieldName = Trim(.Name)
sSrcFldValue = Trim(.Value) & ""
End With

sDstFldValue = Trim(dstRs.Fields(sFieldName).Value) & ""

If dstRs.Supports(adUpdate) Then
If LenB(sDstFldValue) <= 0 And LenB(sSrcFldValue) > 0 Then
dstRs(sFieldName).Value = sSrcFldValue
iLoop = iLoop + 1
ElseIf sDstFldValue <> sSrcFldValue Then
dstRs(sFieldName).Value = sSrcFldValue
iLoop = iLoop + 1
End If
End If
Next iCounter
End If

With dstRs
If .EditMode <> adEditNone Then
iLoop3 = iLoop3 + 1
If iLoop3 Mod 10 = 0 Then
.UpdateBatch
End If
End If
End With

srcRs.MoveNext
If iLoopCtr Mod 100 = 0 Then
DoEvents
End If
Loop
dstRs.UpdateBatch

Yeah, I have posted on the SQL team too. I have just checked it, One more thing is, this code should be generic. I mean the data transfer can be between any two tables.

I would look through dynamic sql and check if it increases my performance. Thank you.

quote:Originally posted by royv

The last question you have asked, did you also post on sqlteam.com? I answered that question there.


"How do you expect to beat me when I am forever?"

Hello royv,

I cann't build dynamic sql, as these tables can be any two tables. I even would not be knowing how many columns need to be updated in the destination table. Comming to memory, I even tried this on dual processor server with good amount of RAM, but I did not see much difference, it is still slower.

Thank you,

quote:Originally posted by royv

The last question you have asked, did you also post on sqlteam.com? I answered that question there.


"How do you expect to beat me when I am forever?"

You must know the 2 table names somehow? Can they not be passed in as parameters to a stored procedure? You can know the columns after you have examined the records that need to be updated in the destination table, which should be good enough to use for dynamic sql, or am I missing something?


"How do you expect to beat me when I am forever?"