Creating indexes on sql server 2012 web version

I am trying to optimize a very simple sql server 2012 (web version) database to avoid transaction errors.  

First, the process:
1) I upload data from a local application to the sql server instance running on my web server.  It identifies approaching finishers for a race.  This is driven by a timer on a vb6 desktop application that updates finisher data to the server every 5 seconds.  There could be anywhere from 0 to 100 records to update, depending on finisher density.  The records are already in the database...just updated as the runners approach the finish line.

2) The announcer has a tablet that queries the data and announces the finishers as, or shortly before, they finish.  He or she refreshes the page at their discretion and reads the finishers.

The problem is that, for larger races in particular, there can be transaction errors either on the web interface used by the announcer or on the vb6 app that updates the data indicatng that it has been chosen as the transaction lock victim.

I have been told that indexing the fields on the db would be helpful but I do no know which fields, what type of index, and how to implement an index.  Note that the AnnouncerPartsID is the primary key.

Here is the vb6 update code:
    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

Open in new window


Here is the announcer query from the web page:
        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


Here is the data table structure:
AnnouncerPartsID      bigint      Unchecked
RaceID      bigint      Unchecked
RaceName      varchar(15)      Checked
ReadNum      int      Unchecked
ReadTime      decimal(18, 3)      Unchecked
PartName      varchar(50)      Unchecked
Bib      int      Unchecked
Age      int      Unchecked
MF      varchar(1)      Unchecked
City      varchar(50)      Checked
St      varchar(50)      Checked

Any help would be much appreciated!
Bob SchneiderCo-OwnerAsked:
Who is Participating?
 
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Hello,

Your reading data form file and updating information row by row using ADODB.Recordset.

So for an instance let's assume, if you are updating data and number of rows are 100 or more than 100. It will execute one by one.

At the time of update it will acquire lock on table, mean while it some try to refresh web page to see updated information it will try to get read lock on table and it will create issue.

You can overcome this by two way.

1) Rather than updating records one by one. Create Stored procedure and pass require values and eventids to stored procedure and update in one go. It will faster and for some hundreds of record it will be very faster.

In you select query mention "WITH (NOLOCK)" for the table.  Putting WITH (NOLOCK) will have some implication like " user will see the data which are not committed". This will not have big impact as you are not using transaction control.

Since you are updating all data in one batch, Chances to read uncommitted data is very less.

2) If you do not want to update data in one batch. You are fine with dirty read.
You can simply put

"WITH (NOLOCK)" in your select query .



Hope it will help you.
1
 
Bob SchneiderCo-OwnerAuthor Commented:
Is there any kind of a down side with using NOLOCK?

Based on my query, would you be willing to show me how the stored procedure would work?
0
 
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Only down side of using NOLOCK is the dirty read but in your case you are not maintaining any transaction so it will not have big impact.
1
 
Bob SchneiderCo-OwnerAuthor Commented:
Thank you!
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.