Data from local sql server to remote sql server using vb6

I have an "Announcer's Portal" that gets data from a vb6 program to a remote server to show the announcer runners soon to finish.  I want to ensure that it is doing so as quickly as possible.  Please look at my code and let me know if there is any way to speed it up:

    iFileNum = FreeFile()

    Open sAncrFile For Input As iFileNum
    sFileData = Input(LOF(iFileNum), #iFileNum)
    Close iFileNum
    LineArr = Split(sFileData, vbCrLf)
    sFileData = vbNullString

    For i = 0 To UBound(LineArr) - 1
        strFields = Split(LineArr(i), ",")
        If Len(strFields(1)) <= 4 Then  'to ensure that no invalid data is processed
            iBib = CInt(strFields(1))
            sngTime = ConvertToSeconds(Replace(strFields(3), """", ""))

            If sngTime > sngStartTime + iMinTime - iDelay Then
                If sngTime > sngLastAncrTime Then
                    If BibExists(iBib) = False Then
                        If BibAsgnd(iBib) = True Then
                            AncrData(0, iNumRcds) = iBib

                            'get part data
                            Set rs = New ADODB.Recordset
                            sql = "SELECT pr.RaceID, p.FirstName, p.LastName, pr.Age, p.Gender, p.City, p.St "
                            sql = sql & "FROM PartRace pr INNER JOIN Participant p ON pr.ParticipantID = p.ParticipantID "
                            sql = sql & "WHERE pr.Bib = " & iBib & " AND pr.RaceID IN (" & sEventRaces & ")"
                            rs.Open sql, conn, 1, 2
                            If rs.RecordCount > 0 Then
                                lRaceID = rs(0).Value
                                AncrData(1, iNumRcds) = Replace(rs(1).Value, "''", "'") & " " & Replace(rs(2).Value, "''", "'")
                                AncrData(2, iNumRcds) = rs(3).Value
                                AncrData(3, iNumRcds) = rs(4).Value
                                If Not rs(5).Value & "" = "" Then AncrData(4, iNumRcds) = Replace(rs(5).Value, "''", "'")
                                AncrData(5, iNumRcds) = rs(6).Value
                            End If
                            Set rs = Nothing
                            AncrData(6, iNumRcds) = GetRaceName(lRaceID)
                            AncrData(7, iNumRcds) = sngTime
                            iNumRcds = iNumRcds + 1
                            ReDim Preserve AncrData(7, iNumRcds)
                        End If
                    End If
                End If
            End If
        End If
    Next i
    For i = iFirstNew To iNumRcds - 1
        If CStr(AncrData(1, i)) = vbNullString Then
            Exit For
            If CSng(AncrData(7, i)) > sngLastAncrTime Then
                sql = "INSERT INTO AnnouncerReads(Bib, PartName, Age, Gender, City, St, Race, RdrTime, EventID) VALUES ('"
                sql = sql & AncrData(0, i) & "', '" & Replace(AncrData(1, i), "'", "''") & "', '" & AncrData(2, i) & "', '" & AncrData(3, i) & "', '"
                sql = sql & AncrData(4, i) & "', '" & AncrData(5, i) & "', '" & AncrData(6, i) & "', '" & AncrData(7, i) & "', "
                sql = sql & lEventID & ")"
                Set rs = srvr_conn.Execute(sql)
                Set rs = Nothing
            End If
        End If
    Next i

Open in new window

Bob SchneiderCo-OwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
I would not do the PartRace and Participant query separately via a RecordSet. I would join them to one huge INSERT statement with SELECT/UNION ALL statements rather than VALUES.

Or I would INSERT into a holding table, then INSERT into your AnnouncerReads table, joining your PartRace and Participant tables at that time.

However, that will take some revision, but it will be a screamer in terms of speed.

Here is some pseudo-code of a suggested revision:
'   Do this before you start the loop, order it by (1) those fields
'   from your input file, then (2) by the data from PartRace
'   and/or Participants

sql = "INSERT INTO AnnouncerReads(Bib, PartName, Age, Gender, City, St, Race, RdrTime, EventID) "
sql = sql & "SELECT InputFileFields.Bib, PartName, Age, Gender, City, St, Race, RdrTime, EventID "
sql = sql & "FROM ("

For i = iFirstNew To iNumRcds - 1
        If CStr(AncrData(1, i)) = vbNullString Then
            Exit For
            sql = sql & "SELECT '" & FileInputField1 & "' as Bib, "
            sql = sql & "'" & FileInputField2 & "' as Whatever "
            If i < (iNumRcds - 1) Then sql = sql & "UNION ALL "
        End If
Next i

sql = sql & ") AS InputFileFields "
sql = sql & "INNER JOIN PartRace pr ON pr.Bib = InputFileFields.Bib "
sql = sql & "INNER JOIN Participant p ON pr.ParticipantID = p.ParticipantID "

Open in new window

Do you get the idea?

You'll need to prefix your fields properly to get this to work. And I don't know where you build your pr.RaceID IN (" & sEventRaces & ")", so you'll need to bring that in.

Once you're done, you should be able to run one and only one query that does it all.

Now, the big question is: How many records usually come in via your file? If too many, the SQL may become too big, and you may have issues there.

if that is the case, you may want to load just your input file data into a holding table, then INSERT into your AnnouncerReads table, JOINing your PartRace and Participant tables to your holding table.

So you have two options, both which are incomparably faster:

1. Build the entire SQL inserts from your input file, joining your ancillary tables in the insert statement.
2. Insert into a holding table first, then do a final insert joining your ancillary tables in the insert statement.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob SchneiderCo-OwnerAuthor Commented:
I like option two because I am not confident that my database is very well designed.  Thank you.  Your pseudo-code looks like option 1.   What would the holding table look like?
dsackerContract ERP Admin/ConsultantCommented:
Yes, the pseudo-code resembled option 1, but I thought of option 2 after I coded it *lol*. That's the beauty of piloting.

You would create your holding table with just the fields that come from your input file, then you'd INSERT them (along with your JOINed tables) into your AnnouncerReads.

Now that I'm writing this, there is a third option. Have you ever looked into BCP or BULK INSERT (the batch utility load programs for SQL Server)? You wouldn't even have to use VB6 to load your holding table. You can load it straight from the file. You'd have to read up on how to use either (BCP is the external version, but BULK INSERT can be called from within T-SQL). There is lots of resources online for both.

Regardless of whether you want to tackle that or simply load a holding table via your VB6, simply make the table with just the fields in your input file, then join your ancillary tables.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Anthony PerkinsCommented:
I would do the following:
1.  Build a Stored Procedure that takes just one parameter (an Xml document) and has a single INSERT statement that shreds the Xml document into the corresponding columns using either OPENXML or an Xml data type method.
2.  In VB6 build the Xml document with all the necessary values.
3.  Call the Stored Procedure with the Xml document.

The big advantage of this approach aside from performance is that you are always passing a single parameter: The signature of the Stored Procedure never changes, regardless of the number of times you change the schema to the table.

Let me know if you want more details.
Anthony PerkinsCommented:
You can certainly add the rows to a table before doing the insert however there are some caveats to that approach:
1.  You still are doing a single insert for every row you are adding, so unless the database is on the same server (highly unlikely nowadays), that means you are going to take a hit every time you send data across the network.  Unless you are dropping the table at the end (you need permissions to do that) you will have to empty it as well (another performance hit).
2. If this is a multi-user app you will have to take that in account and come up with some scheme to support that.
3, It requires more permissions.
Bob SchneiderCo-OwnerAuthor Commented:
I am going to begin with the holding table initially because I need this ready on Saturday.  Thanks.  Having said that, a bigger problem is that, as we time larger and larger events, the number of rows in our RFID text file can get in the tens of thousands or more (hopefully :) ) so the Bulk Insert sounds attractive.  Could I use it tparse and write a text file like below to the db?  Currently I am using a LineArr and inserting the data line-by-line (see my initial post on this thread).

Finally, the stored procedure/xml document is intriguing?  I think I will re-open a question comparing the two with larger data files as below to see which one would be quicker.  Note that we never have to write to multiple servers or have multiple users, which raises another question...for another post.


Open in new window

dsackerContract ERP Admin/ConsultantCommented:
Actually, the stored procedure can BULK INSERT and subsequently INSERT to the AnnouncerReads table. It can do it all.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Bob, do you still need help with this question?
Bob SchneiderCo-OwnerAuthor Commented:
I don't think so.  I am going to accept multiple and then look into completely restructuring how I handle large numbers of records in a data file.  Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.