SQL Server Performance

Server side cursor

Discussion in 'T-SQL Performance Tuning for Developers' started by maddalimurali, Nov 18, 2002.

  1. maddalimurali New Member

    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
  2. royv New Member

    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?"
  3. maddalimurali New Member

    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?"
  4. satya Moderator

    Using client-side cursor can fetch you the error defiened above, and this occurs when you have no primary key defined in your dataset.

    Moreover don't use floating point and date/time fields in the primary key. Use fixed lenght numeric/decimal fields instead.

    In my experience using server side cursor is better than using from client-side.

    HTH


    Satya SKJ
  5. royv New Member

    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?"
  6. maddalimurali New Member

    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
  7. royv New Member

    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?"
  8. maddalimurali New Member

    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
  9. maddalimurali New Member

    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?"
  10. maddalimurali New Member

    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?"
  11. royv New Member

    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?"

Share This Page