Check if records exist

rufen101
10-20-2004, 08:37 AM
I load data from a file into a table and I need to check if the record already exist in that table. If so, I update the record and if the record is not present in the table then I add it.

I made a function that check if the record exist but it take too much time because there is too much record in the table.

Here's the function


Private Function Exist(inDate As String, inTag As String) As Boolean
Dim rs As New ADODB.Recordset

rs.Open "Select * from table1 where aDate like '" & inDate & "' and tag like '" & inTag & "'", con, adOpenKeyset, adLockOptimistic

If rs.EOF And rs.BOF Then
Exist = False
Else
Exist = True
End If
rs.Close
Set rs = Nothing
End Function


Is there another way to check if a record exist?

Shurik12
10-20-2004, 08:47 AM
Could you try the following instead:


rs.Open "Select Count(*) from table1 where aDate like '" & inDate & "' and tag like '" & inTag & "'", con, adOpenForwardOnly, adReadOnly

rufen101
10-20-2004, 08:57 AM
I tried it, but it still take too much time. I also tried to change the cursor to fowardOnly and it didn't work.

Any other ideas..

Shurik12
10-20-2004, 08:59 AM
What do you mean by "too much time"?

What DB are using? Is it remoted or not? How big is the recordset you're returning?

rufen101
10-20-2004, 09:09 AM
I use Access 2000 for development purpose, but the final version of the app will use an Sql Server database. The recordset has approximatively 100 000 records.

Is there a Replace function that would Create a new record if there's no match and update the record if it finds one?

By "too much time" I mean that is takes about 10 minutes to load the data if I use the Exist Function. If I don't use it and just skip errors, then it takes 15 seconds*.

Shurik12
10-20-2004, 09:20 AM
>10 minutes to load the data ....

well it's indeed a lot (to say the least)
how and at what moment are you opening your connection? there's definitely something wrong going on...

rufen101
10-20-2004, 09:32 AM
I open the connection at the formLoad. The goal of this module is only to read data from CSV file and load it into a table. It is really simple. Here's the whole code of it.


Option Explicit
Private con As New ADODB.Connection

Private Sub cmdDelete_Click()
con.Execute ("Delete * from table1")
lblAffichage = "Deletion completed"
End Sub

'Read each CSV files and load them into the table.
Private Sub cmdExecute_Click()
Dim fHandle As Integer
Dim fileName As String
Dim aValue() As String
Dim aLine As String
Dim doInsert As Boolean
Dim StartTime As Date
Dim rs As New ADODB.Recordset

StartTime = Now

rs.Open "table1", con, adOpenKeyset, adLockOptimistic
fileName = Dir(App.Path & "\CSV\")
While fileName <> ""
If LCase$(Right$(fileName, 3)) = "csv" Then
lblAffichage = fileName
DoEvents
fHandle = FreeFile
Open App.Path & "\CSV\" & fileName For Input As fHandle
Line Input #fHandle, aLine
While Not EOF(fHandle)
Line Input #fHandle, aLine
aValue = Split(aLine, ",")
If aValue(2) <> "" Then
If Not Exist(aValue(0), aValue(1)) Then
rs.AddNew
doInsert = True
Else
If Check1.Value = 1 Then 'This check specify wheter or not the data is overWritten
doInsert = True
Else
doInsert = False
End If
End If
If doInsert Then
rs!aDate = aValue(0)
rs!tag = aValue(1)
rs!valeur = aValue(2)
rs.Update
End If
End If
Wend
Close fHandle
End If
fileName = Dir
Wend
fHandle = FreeFile
rs.Close
Set rs = Nothing
lblAffichage = "Done: " & CDate(Now - StartTime) & " hours "
End Sub

Private Sub cmdQuit_Click()
Unload Me
con.Close
Set con = Nothing
End Sub

Private Sub Form_Load()
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;User Id=admin;Password="
End Sub

'Check if the record already exist in the table
Private Function Exist(inDate As String, inTag As String) As Boolean
Dim rs As New ADODB.Recordset

rs.Open "Select count(*) from table1 where aDate like '" & inDate & "' and tag like '" & inTag & "'", con, adOpenForwardOnly, adLockReadOnly

If rs.EOF And rs.BOF Then
Exist = False
Else
Exist = True
End If
rs.Close
Set rs = Nothing
End Function

Shurik12
10-20-2004, 09:59 AM
OK I did not you were calling Exists in a loop.

I'd suggest the following then

-load the whole thing from the csv to a temporary table (15 sec according to you right)
-execute the SQL statement in the lines with
INSERT INTO ....
SELECT ...
WHERE EXISTS...

Dennis DVR
10-21-2004, 03:45 AM
using a "like" operator without a wildcard symbol makes the query slow, and calling the DoEvents in a loop will took a lot of the processing time.

and shurik's suggestion would be faster instead of using the exist function.

NEOLLE
10-21-2004, 05:05 AM
Hello rufen101,
I think the problem is that you are using a Like condition, and is your aDate properly indexed?:)

rufen101
10-21-2004, 08:19 AM
I finally found the solution based on Shurik12 suggestion. I use a temporary table to load the data from the CSV file. Then I update the data in the Destination table based on the records in the Temporary table. Finally, I copy new records into the Destination table.

Thanks guys for you precious help.

loquin
10-21-2004, 10:39 AM
You've also got to remember, Access is a client side cursor database ONLY. This means, that for each and every query, the entire TABLE(s) associated with the query are copied from the database .MDB file to the Client ADO. Even IF the database is local, copying 100K records will take some time. The fact taht you're using the LIKE operator will increase the time as well.

For starters, I would suggest that you install MSDE on your desktop to be used as the development database engine, rather than Access. Since the database will migrated anyway, you'll save youself some time when you go full-scale. Your application then won't have to change one whit. In addition, since SQL Server queries, when run with server side cursors, are very effecient, that in itself should speed up your queries.

Another place to look is how your tables are indexed. Appropriate indexing can speed up your queries by large factors.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum