Question Why ExecuteNonQuery is very slow,

[chuoitieu]

Dear all,

I am working on a routine to insert data from an access table to another table. The two tables have the same schema.

Public Function InsertCount(ByVal sCon As OleDbConnection) As Integer
        Dim strsql As String = "Insert Into Student" & vbcrlf & "Select * from Student_Temp Where StudentID Not In (Select StudentID from Student)"
     
        Dim cmd As New OleDbCommand(strsql, sCon)
        cmd.CommandTimeout = 0
        Try
            If sCon.State <> ConnectionState.Open Then sCon.Open()
            Dim i As Integer = cmd.ExecuteNonQuery
            Return i
        Catch ex As Exception
           Return 0
        Finally
            sCon.Close()
        End Try
    End Function

The problem is that it runs very well when table Student is empty or not so full. Where there are about 20,000 records in each table, it takes a long time to finish. I have tried and it takes now ~40 min and still not finish yet.

Can someone help me with how to speedup this process?

Thank you very much,

 

[Robert_Zenz]

Hello.

Have you assigned an index onto StudentID? If not, do so, it will speed up the process. Also, since the StudentID is most likely unique, make it an unique index and try if this is faster:

REPLACE INTO student SELECT * FROM student_temp

Bobby

 

[InertiaM]

WHERE ... NOT IN

is inherently slow. This may be better

INSERT INTO
    Student
SELECT
    *
FROM
    Student_Temp
LEFT JOIN Student ON Student_Temp.StudentID = Student.StudentID
WHERE
  Student.StudentID IS NULL