I know about indexing, did that prior to anything, however, the time needed to complete the operation is enormous. Here is the code (a bit altered but it is pretty much the same)
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Database.accdb;Persist Security Info=False"
Dim con As New OleDb.OleDbConnection
Dim command As New OleDb.OleDbCommand
Dim sql As String
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
con.Open()
For i = 1 To 200000000
Randomize()
randv = CInt(Int(200000000 * Rnd() + 1))
sql = "SELECT Count(*) FROM Table1 WHERE Column1='" & randv & "'"
command = New OleDb.OleDbCommand(sql, con)
count = command.ExecuteScalar()
If count = 0 Then
sql = "INSERT INTO Table1 VALUES ('" & randv & "')"
command = New OleDb.OleDbCommand(sql, con)
command.ExecuteNonQuery()
Else
i -= 1
End If
Application.DoEvents()
Next i
con.Close()
End Sub
There is just this one column in the table.
Ok, now I've coded in a 5 second timer that will tell me estimated time remaining for all 200000000 entries to be inserted and it says
6 days.
To even more complicate the things the data entered into the database are not numbers but strings.
Any faster solutions or i just have to live with this.
I could try SQL but i really doubt there would be a significant change.
Is there a better(faster) way to store this kind of data other than a database (this should be a part of a web application, however this is coded for testing purposes only).