I am trying to do a check if the record already exists in my database. I wrote this code and all is well except it takes about 2-5 seconds to execute on my access database. Wen you know this one is called about 30 000 to 50 000 times that is unacceptable.
Can someone please take a look and see if this can be accelerated?
Public Function CheckDouble(psWeb As String, psIP As String, pdDate As Date, _
pdTime As Date, psEnvironment As String, psControler As String, _
plType As Long, plSize As Long, pbSpecial As Boolean, plSpend As Long) As Boolean
Dim rs As ADODB.Recordset
Dim sql As String
sql = "select count(ip) as cnt from Logs where [IP] = '" & psIP & "'" _
& " And [Web] = '" & psWeb & "'" _
& " And [Date] = #" & Format(pdDate, "MM/DD/YY") & "#" _
& " and [Time] = #" & Format(pdTime, "HH:MM") & "#" _
& " and [Environment] = '" & psEnvironment & "'" _
& " and [Controler] = '" & psControler & "'" _
& " and [Spend] =" & plSpend & "" _
& " and [Type] =" & plType & "" _
& " and [Special] = " & CBool(pbSpecial) & "" _
& " and [Size] =" & plSize
On Error GoTo CheckDouble_Error
Set rs = oCon.Execute(sql)
On Error GoTo 0
If rs.Fields("cnt").Value > 0 Then
CheckDouble = True
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure CheckDouble of Class Module cLogDatabase"
This is an example of the SQL statement I get
select count(ip) as cnt from Logs where [IP] = '220.127.116.11 ' And [Web] = 'WEB2' And [Date] = #07/01/04# and [Time] = #14:14# and [Environment] = 'AON' and [Controler] = 'EAFormController' and [Spend] =2 and [Type] =200 and [Special] = False and [Size] =23489
My first suggestion would be to look at adding indices to make it easier for Jet to process the query. You may well need to experiment a bit to find a good combination of columns, since Jet often makes "interesting" choices where indicies are concerned.
If that doesn't help enough, I'd switch to MSDE in order to get more help understanding the query itself. It is a lot easier to find and fix query problems in MSDE than it is in Jet, and once you've solved the problem you can almost always move back to Jet if you want.