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
gr8lifeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hatem72Commented:

here is my way ...i think its fine

  Public Function GetOneValue(ByVal Sqls As String) As String

        '

        Try

            ' Assumes that connection is a valid SqlConnection object.
            Dim SqlCon As New SqlClient.SqlConnection(v_ConnectionString)
            Dim sComand As SqlCommand = New SqlCommand(Sqls, SqlCon)
            Dim Result As String

            SqlCon.Open()
            Result = sComand.ExecuteScalar()
            If Result = Nothing Then
                Return "Null Value"
            Else
                Return Result
            End If
            SqlCon.Close()

        Catch ex As Exception
            Return ex.Message
        End Try

    End Function
PockyMasterCommented:
Well, you could make the database function reusable, and enter your queries into the function, instead of creating a database connection function for each query you want to perform. That will save a lot of coding.
I don't understand what you're doing with the tablemapping stuff.
But by taking your function as a start


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  <---- remove
        Dim da As New OleDbDataAdapter
        Dim cmdSelect As New OleDbCommand
        Dim ds As New DataSet <--remove
        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")  <--- remove

'you might do the concatenation with a stringbuilder instead of '&'
'in theory it might be faster
        cmdSelect.CommandText = "SELECT countryname FROM Country WHERE " & lngIP.ToString & " >= ipl1 and " _
                   & lngIP.ToString & "  <= ipl2"

        cmdSelect.Connection = con
        da.SelectCommand = cmdSelect
        da.Fill(dt) <-- fill datatable instead of dataset
        If dt.Rows.Count = 0 Then
            strCountry = "Unknown"
        Else
            strCountry = dt.Rows(0).Item("countryname")
        End If

        Return strCountry
    End Function


And if you want to win some more performance, try thinking about how many times you connect /disconnect to your database.
And how many times you actually need to requery, you might cache it somewhere
dancebertCommented:
It may not be just a code issue.  Do you have an indexes on each of the two fields (LIP1, LIP2) in your Where clause?

To expand on something PockyMaster said
> try thinking about how many times you connect /disconnect to your database

Getting connections is slow.  If IPLookup() is being called more than a few times you could do something like this:

' dim and create myConnection

' for all the IPs to be looked up
'   result = IpLookup(IpNumber, myConnection)
'   do some work with the result
' next

' close myConnection.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

cubixSoftwareCommented:
Here's my two-penth....

  Public Function IPLookup(ByVal DottedIP As String) As String
        Dim lngIP As Long
        Dim con As New OleDbConnection
        Dim cmdSelect As New OleDbCommand
        Dim dr As OleDbDataReader
        Dim strCountry as String

        lngIP = Dot2LongIP(DottedIP)
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\resource\master.mdb"
        con.open

        With cmdSelect
            .Connection = con
            .CommandText = "SELECT countryname FROM Country WHERE @longIP BETWEEN ipl1 and ipl2"
             .CommandType = CommandType.Text
            .Parameters.Add(New OleDbParameter("@longIP", lngIP)))
            dr = .ExecuteReader
        End With

        If dr.HasRows Then
            dr.Read()
            strCountry = dr.Item("countryname")
        Else
            strCountry = "No value"
        End If

        dr.Close()
        con.Close()

        Return strCountry

    End Function


Microsoft recommend the data reader for quick readonly access

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp


HTH :)
gr8lifeAuthor Commented:
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
gr8lifeAuthor Commented:
Thank you very much for all your help!
Gr8life
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.