Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: Check for duplicates

    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?

    Thanks

    Napivo

    Code:
    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
      End If
      Exit Function
    CheckDouble_Error:
    
      Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure CheckDouble of Class Module cLogDatabase"
    End Function
    This is an example of the SQL statement I get

    Code:
    select count(ip) as cnt from Logs where [IP] = '194.235.127.40 ' 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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •