Link to home
Start Free TrialLog in
Avatar of gr8life
gr8life

asked on

Performance Improvements

I am currently using the code below in an application which I am trying to enhance performance.  I am looking for suggestions for improving performance of this code. The database being used has one table named Country, with three columns named LIP1, LIP2, and countryname.

    Public Function IPLookup(ByVal DottedIP As String) As String
        Dim lngIP As Long
        Dim strCountry As String
        Dim con As New OleDbConnection
        Dim dtm As DataTableMapping
        Dim da As New OleDbDataAdapter
        Dim cmdSelect As New OleDbCommand
        Dim ds As New DataSet
        Dim dt As DataTable

        lngIP = Dot2LongIP(DottedIP)
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\resource\master.mdb"
        dtm = da.TableMappings.Add("Table", "Country")
        cmdSelect.CommandText = "SELECT countryname FROM Country WHERE " & lngIP.ToString & " >= ipl1 and " _
                   & lngIP.ToString & "  <= ipl2"

        cmdSelect.Connection = con
        da.SelectCommand = cmdSelect
        da.Fill(ds)
        dt = ds.Tables("Country")
        If dt.Rows.Count = 0 Then
            strCountry = "Unknown"
        Else
            strCountry = dt.Rows(0).Item("countryname")
        End If

        Return strCountry
    End Function
End Class


Thank you very time and expertise,
Gr8life
SOLUTION
Avatar of hatem72
hatem72

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gr8life
gr8life

ASKER

After reading the great approaches posted here I realized I am call the database twice per line.  The smallest files have to process have at least 60,000.  So I believe this is the biggest problem with performance.  Also I did not have an indexes on each of the two fields (LIP1, LIP2).  I am going to try all the solutions posted tonight.
Thank you for the great advice,
Gr8life
Avatar of gr8life

ASKER

Thank you very much for all your help!
Gr8life