Database throws error when processing results

I have an announcer's portal that indicates incoming finishers in running races before they finish.  The timing equipment populates a text file on my computer.  Then I have a vb6 app that pulls the most recent lines of text from the text file and sends them to the server.  It does this via a timer every 5 seconds.  Here is the code for that:

    
    iLastRead = 0
    sngLastTime = 0
    
    Set rs = New ADODB.Recordset
    sql = "SELECT Top 1 ReadNum, ReadTime FROM AnnouncerParts WHERE RaceID IN (" & sEventRaces & ") AND ReadNum > 0 "
    sql = sql & "ORDER BY ReadNum DESC, Bib"
    rs.Open sql, conn, 1, 2
    If rs.RecordCount > 0 Then
        iLastRead = rs(0).Value
        sngLastTime = rs(1).Value
    End If
    rs.Close
    Set rs = Nothing
    
    If sngLastTime = 0 Then sngLastTime = ConvertToSeconds(sStartTime)

    iFileNum = FreeFile()

    Open sAncrFile For Input As iFileNum
    sFileData = Input(LOF(iFileNum), #iFileNum)
    Close iFileNum
    
    LineArr = Split(sFileData, vbCrLf)
    sFileData = vbNullString
    
    For i = iLastRcd To UBound(LineArr) - 1
        strFields = Split(LineArr(i), ",")

        sThisBib = strFields(1)
        
        lstRawReads.AddItem LineArr(0)
        
        If sSixDigitsOnly = "n" And Len(sThisBib) <= 4 Then sThisBib = CStr(Int(sThisBib) + 100000) 'make it 6 digits
        
        If Len(sThisBib) = 6 Then
            sThisBib = CLng(sThisBib) - 100000
            iBib = CInt(sThisBib)
            sTime = Replace(strFields(3), Chr(34), "")
            sngTime = ConvertToSeconds(sTime)
            
            If sngTime > sngLastTime Then
                'update data
                Set rs = New ADODB.Recordset
                If sAllowMultiple = "yes" Then
                    sql = "SELECT ReadNum, ReadTime FROM AnnouncerParts WHERE Bib = " & iBib & " AND RaceID IN (" & sEventRaces & ")"
                Else
                    sql = "SELECT ReadNum, ReadTime FROM AnnouncerParts WHERE Bib = " & iBib & " AND RaceID IN (" & sEventRaces
                    sql = sql & ") AND ReadNum = 0 ORDER BY ReadNum DESC"
                End If
                rs.Open sql, conn, 1, 2
                If rs.RecordCount > 0 Then
                    rs(0).Value = iLastRead + 1
                    rs(1).Value = sngTime
                    rs.Update
                    
                    iLastRead = iLastRead + 1
                End If
                rs.Close
                Set rs = Nothing
            End If
        End If
    Next i
    
    'this lets us know where to start looking in the text file
    iLastRcd = i
    
    Set rs = New ADODB.Recordset
    sql = "SELECT LastRcd FROM Announcer WHERE EventID = " & lEventID
    rs.Open sql, conn, 1, 2
    rs(0).Value = iLastRcd
    rs.Update
    rs.Close
    Set rs = Nothing

Open in new window


The person announcing the incoming finishers reads from a web interface (http://www.gseannouncer.com/default.asp?event_id=750&which=announcer), periodically refreshing using the orange button.  The code for that is here:

       Set rs = Server.CreateObject("ADODB.Recordset")
        If Cint(iNumToShow) = 0 Then
            sql = "SELECT Bib, PartName, Age, MF, City, St, RaceName FROM AnnouncerParts WHERE RaceID IN (" & sEventRaces & ") "
            sql = sql & "AND ReadNum > 0 ORDER BY ReadNum DESC"
        Else
            sql = "SELECT TOP " & iNumToShow & " Bib, PartName, Age, MF, City, St, RaceName FROM AnnouncerParts WHERE RaceID IN (" & sEventRaces 
            sql = sql & ") AND ReadNum > 0  ORDER BY ReadNum DESC"
        End If
        rs.Open sql, conn, 1, 2
        If rs.RecordCount > 0 Then
            AncrData = rs.GetRows()
        Else
            ReDim AncrData(6, 0)
        End If
        rs.Close
        Set rs = Nothing

Open in new window


And here is how it is displayed:
            <table class="table table-striped">
                <tr><th>Bib</th><th>Name</th><th>Age</th><th>M/F</th><th>City</th><th>St</th><th>Race</th></tr>
                <%For i = 0 To UBound(AncrData, 2)%>
                    <tr>
                        <td><%=AncrData(0, i)%></td>
                        <td><%=AncrData(1, i)%></td>
                        <td><%=AncrData(2, i)%></td>
                        <td><%=AncrData(3, i)%></td>
                        <td><%=AncrData(4, i)%></td>
                        <td><%=AncrData(5, i)%></td>
                        <td><%=AncrData(6, i)%></td>
                    </tr>
                <%Next%>
            </table>

Open in new window


It works very well except in big races.  Then it tends to have periodic errors when it is updating the server with data from the local machine while the announcer is trying to access it to announce finishers.  Is there a way that I can "clean up" my queries to avoid this issue?

Thanks in advance!
Bob SchneiderCo-OwnerAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need at least an index on RaceID and ReadNum columns.
If the PK is RaceID then you'll only need to create and index on ReadNum column.

Anyway, deadlock doesn't occurs on SELECTs so some write process (INSERT, UPDATE or DELETE) is running simultaneously on AnnouncerParts table
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like you need to tune your queries.
I can see ORDER BY clauses that are really heavy, especially when working with large amount of data.
Review the table indexes. All columns used in the WHERE clauses of the more used queries, should be indexed.
0
 
Bob SchneiderCo-OwnerAuthor Commented:
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Can I set the indexes in SSMS?  Right now I have a primary key only.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.