• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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
0
gr8life
Asked:
gr8life
4 Solutions
 
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
0
 
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
0
 
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 :)
0
 
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
0
 
gr8lifeAuthor Commented:
Thank you very much for all your help!
Gr8life
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now