Strange happenings with SQLDataReader

[rustyd]

I am performing a routine where I load a SQLDataReader with a tables contents using SELECT * FROM tblname. I then loop through the tables checking items like customer and user against other tables we have. If the customer/user exists get the next record, else prompt to delete. If they choose to delete, I execute a DELETE FROM command on the current record.

 

Let me first say, we do not have foreign key constraints added to enforce integrity, and we don't have CASCADE DELETES setup either. There is no plan to do so either.

 

My problem was this: we have 1 table that has 46,000 records. Another has 7,000 records. Both have records that no longer need to be kept so we perform the steps I mentioned above.

 

The table (Comments) with 7,000 records has the following format:

Number (int)

Date (DateTime)

Comment (VarChar 2048)

Subject (VarChar 100)

MyGuid (Guid)

 

The table (CallLog) with 46,000 records has the following format:

Number (int)

StartDate (varchar 14)

RealDate (varchar 14)

Version (varchar 5)

Reasons (varchar 2048)

Minutes (smallint)

Message (varchar 2048)

CSIUser (varchar 12)

Contact (varchar 30)

Answered (bit)

Subject (varchar 100)

MyGuid (Guid)

 

OK, so I'm looping through the Comments table and check the Number against our CustomerMaster table and find that the CustomerMaster does not exist. So I perform the DELETE FROM Comments command from a separate SQLCommand and SQLConnection and it deletes the current SQLDataReader record with no problems.

 

OK, so I'm looping through the CallLog table and check the Number against our CustomerMaster table and find that the CustomerMaster record does not exist. So I perform the DELETE FROM CallLog command, again from a separate SQLCommand and SQLConnection and it times out. I look in the Enterprise Manager (see attached picture) and it shows the spid for the SQLDataReader as Blocking and the spid for the DELETE FROM command as being blocked by spid xx (the SQLDataReader spid).

 

OK, so by elimination I test SELECT TOP 32000 * FROM CallLog. It takes 20 seconds before returning results. I then say SELECT TOP 32000 Number, StartDate, CSIUser, RealDate FROM CallLog and it returns results immediatly. Those are the fields in the index. So, I replaced my SQL Statement for the SQLDataReader with SELECT Number, StartDate, CSIUser, RealDate FROM CallLog ORDER BY .... and NOW itl deletes the record just fine.

 

Has anyone else experienced locking issues with the SQLDataReader? Is there a size limitation on the SQLDataReader? The process is really fast, and we are happy with the results, but puzzled by why it was locked.

[Robby]

Are you executing the Delete while in the DR loop? If so then the DR still has an open conn to the data.

[Robby]

This design has a large overhead, can't you join the three tables in a single select, show the results to the end user, let them select the items to delete by way of checkBoxes and then delete the selected items. Just an idea.

[rustyd]

Yes. And it works. Here is the sub.

 

 Private Sub CheckCall()
   Dim rsCall As SqlDataReader
   Dim _Ret As SQLRet
   Dim lkTot As Long
   Dim DelSQL As String

   Try
     ' set a global sqlconnection
     Conn = ConnDB()

     CURREC = 0
     SavePct = String.Empty
     AllDel = False

     lblForm.Text = "Opening Call Log..." : lblForm.Refresh()

     ' uses a sqldatareader saying SELECT Count(*) FROM tbl
     TotRecs = GetRecs("CallLog", "")

     rsCall = GetSQLReader("SELECT Number, StartDate, CSIUser, RealDate " _
        & "FROM CallLog ORDER BY csiuser,StartDate,Number")
     CURREC = 0
     SavePct = String.Empty
     lblForm.Text = "Checking Call Log ..." : lblForm.Refresh()
     If TotRecs > 0 Then
       Do While rsCall.Read
         DelThis = False
         lkTot = GetRecs("CustomerMaster", "WHERE Number = " _
            & rsCall("Number").ToString)
         If lkTot <= 0 Then
           If AllDel Then
             DelThis = True
           Else
             eMsg = "Call log record for # (" + rsCall("Number").ToString " _
               + ") on  (" + Format(CDate(GetSqlDateFromStr(rsCall("StartDate"))), DMYString) _
               + ")" + vbCrLf + " points to an invalid account. " _
               + " What do you want to do?" + vbCrLf + vbCrLf
             PopMsg(eMsg, DelThis, AllDel)
           End If
         End If
         If DelThis Then
           If AllDel Then
             DelSQL = "DELETE FROM CallLog WHERE Number = " & rsCall("Number").ToString
           Else
             DelSQL = "DELETE FROM CallLog WHERE Number = " _
               & rsCall("Number").ToString & " AND StartDate='" & PadSQLField(rsCall("StartDate").ToString.ToUpper) _
               & "' AND RealDate='" _
               & PadSQLField(rsCall("RealDate").ToString.ToUpper) _
               & "' AND CSIUser='" & PadSQLField(rsCall("CSIUser").ToString.ToUpper) & "'"
           End If
           _Ret = ExecDB(DelSQL)
           If _Ret.RetVal Then
             If _Ret.RecAff <> 1 Then
               If AllDel Then
               Else
                 MessageBox.Show("The attempt to delete the call log entry for customer # " _
                 & rsCall("Number").ToString & " on " _
                 & Format(CDate(GetSqlDateFromStr(rsCall("StartDate"))), DMYString) _
                 & " failed. " & _Ret.RecAff.ToString & " records were affected.",  _
                 Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
               End If
             End If
           Else
             MessageBox.Show("The attempt to delete the call log entry for customer # " _
             & rsCall("Number").ToString & " on " _
             & Format(CDate(GetSqlDateFromStr(rsCall("StartDate"))), DMYString) _
             & " failed. " & _Ret.RecAff.ToString & " records were affected.", _ 
             Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
           End If
         End If
         DoEvents()
         If Cancelit Then Exit Do
       Loop
     End If
     If Cancelit = False Then
       ChkRef(0).Checked = False
     End If
   Catch
      ' error handling
   Finally
     rsCall.Close()
     Conn.Close()
     Conn.Dispose()
     lblForm.Text = String.Empty
     Label1.Text = String.Empty
     ProgressBar1.Value = 0
   End Try


 End Sub

[rustyd]

This design has a large overhead' date=' can't you join the three tables in a single select, show the results to the end user, let them select the items to delete by way of checkBoxes and then delete the selected items. Just an idea.[/quote']

 

I am amazed at the speed it processes this at. With the individual SELECT statements in the GetRecs for each record we thought it would take quite a bit longer, but it takes 23 seconds to process 45,404 records on a P4, 2.0 GB w/ 512 MB RAM and a local MSDE.

 

The Left Outer Join was option #2 if the performance on this was poor.

[Robby]

Oh... I thought users were choosing which items to delete, since there is no user intervention I would do this in a stored proc, I realize that you're satisfied with the perfomance but it's still 22 seconds too long.

[Robby]

Here's an example of using a Delete statement with a Join...

 

delete Table1

FROM Table1 INNER JOIN Table2 ON Table1.myID = Table2.myID

WHERE Table2.myID = XXX

 

I suggest creating a Select that includes all three tables then when the data returned is as expected then convert it into a Delete. I guarantee that it will take about a second to execute in or out of a stored proc.

[rustyd]

The user is prompted to delete a specific record or delete all unmatched records with the call below:

 

 PopMsg(eMsg, DelThis, AllDel)