Question Can this be simplified?

[computer guy]

Hello everyone,

I have written a piece of code to compare two DataTables and generate an SQL to update a MYSQL table to reflect the changes.

i.e. If there is a row in DataTable1 and not in DataTable2 it gets added to MYSQL Table.
But if there is a row in DataTable2 and not in DataTable1 it gets removed from MYSQL Table.

This code takes about 6 minutes to execute on a large DataTable and I was wondering if anyone can see an obvious way to improve on the code.

Function compare_data()
Dim MYSQL_String As String = ""
        Dim song As Data.DataRow
        Dim i As Integer = 0
        SongCount = 0
        For Each song In SamData.Rows
            Dim result() As DataRow
            result = WebData.Select("title = '" + song.Item("title").ToString.Replace("'", "''") + "' AND album = '" + song.Item("album").ToString.Replace("'", "''") + "' AND artist = '" + song.Item("artist").ToString.Replace("'", "''") + "'")
            If result.Count = 0 Then
                If i = 0 Then
                    MYSQL_String += "INSERT INTO songs (`title`, `album`, `artist`, `dj_id`) VALUES (`" & song.Item("title") & "`,`" & song.Item("album") & "`,`" & song.Item("artist") & "`,`" & DJ_ID & "`)"
                Else
                    MYSQL_String &= ",(`" & song.Item("title") & "`,`" & song.Item("album") & "`,`" & song.Item("artist") & "`,`" & DJ_ID & "`)"
                End If
                i += 1
                SongCount += 1
            End If
        Next song
        Dim title As New ArrayList
        Dim album As New ArrayList
        Dim artist As New ArrayList
        i = 0
        MYSQL_String += ";"
        For Each song In WebData.Rows
            Dim result() As DataRow
            result = SamData.Select("title = '" + song.Item("title").ToString.Replace("'", "''") + "' AND album = '" + song.Item("album").ToString.Replace("'", "''") + "' AND artist = '" + song.Item("artist").ToString.Replace("'", "''") + "'")
            If result.Count = 0 Then
                title.add(song.Item("title"))
                album.add(song.Item("album"))
                artist.add(song.Item("artist"))
                i += 1
            End If
        Next song
        Dim titleString As String = ""
        Dim albumString As String = ""
        Dim artistString As String = ""
        For t As Integer = 0 To i - 1
            If t = 0 Then
                titleString += "`" + title(t) + "`"
                albumString += "`" + album(t) + "`"
                artistString += "`" + artist(t) + "`"
            Else
                titleString += ",`" + title(t) + "`"
                albumString += ",`" + album(t) + "`"
                artistString += ",`" + artist(t) + "`"
            End If
        Next
        If titleString.Length > 0 Then
            MYSQL_String &= "DELETE FROM songs WHERE `title` IN (" & titleString & ") AND `artist` IN (" & artistString & ") AND `album` IN (" & artistString & ") AND `dj_id` = " & DJ_ID & ";"
        End If
        Return MYSQL_String
End Function

Thank you in advance.

 

[JuggaloBrotha]

Have you ever looked into using a DataSet for database operations instead of building the queries manually in code?

As for simplifying things, you could have the database do this all for you, do a Delete on the one table where the records in the result set aren't in the first table. By that I mean you use a query similar to this:

DELETE FROM Table2 WHERE NOT IN (SELECT * FROM Table1)

Or something similar to that. You can also do something similar with an insert too.

 

[computer guy]

Hey JuggaloBrotha,

Thank you for your quick reply. Unfortunately DataTable1 is getting its data from a local MYSQL database, DataTable2 from an XML document and the final query is being executed on a remote MYSQL database.

So converting the entire thing into a MYSQL query just isn't possible.

 

[computer guy]

[solved]

For those interested I solved my problem of speed by using the StringBuilder to build the SQL query and replaced the DataTable.select query with a "column Not In (' ', ' ')" query.

This meant that only one query was done and strings were not duplicated every time I wanted to append to them, as .NET does with the += and &= operations.

 

[JuggaloBrotha]

You could go one step further and have one string for the query(ies) by just parameterizing them.

Your query would be this:

Dim SqlString As String = "INSERT INTO songs (`title`, `album`, `artist`, `dj_id`) VALUES (@Title, @Album, @Artist, @ID)"
Dim cmd As DbCommand(SqlString , Connection)

    ' Create parameters for the query.
    cmd.Parameters.AddWithValue("@Title", song.Item("title"))
    cmd.Parameters.AddWithValue("@Album", song.Item("album"))
    cmd.Parameters.AddWithValue("@Artist", song.Item("artist"))
    cmd.Parameters.AddWithValue("@ID", DJ_ID)

Though if you use a DataSet, you could probably set up the delete and insert type of queries that I posted previously because you can bring in the xml file's table(s) as one datasource and the actual DB's table(s) as another datasource then just do a query between the two, I don't know how much work it would be (or if it's 100% possible) but the execution time would be something like a split second if it works right.