Limit opening and closing of the database connection

I have been working on an application for several months which utilizes a database to perform IP address lookups.  This application is very slow and I have realized through several postings here that the biggest problem it is encountering is the amount of times the application opens and closes the database connection.  I have been trying to read a table from a database and cache it so it can be reusable. I am relatively new to programming, but am really trying hard to grasp it and at this point I really don’t know if this is the best way to do speed up the application performance, so please help.

Thank you for your time,
Gr8life

I am using an MS Access database which has one table named Country, with three columns named LIP1, LIP2, and countryname.  Also all the fields are indexed accordingly.

Previous posting where I am stuck:

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21784599.html

Below is the fastest working code I have been able to piece together.

Imports System.Data.Oledb
Imports System.Data.Common
Imports System.IO
Imports System.Text

    Public Sub ConvertFiles(ByVal filein As String, ByVal fileout As String)
        Try
            If (IO.File.Exists(filein)) Then
                Dim sin As New IO.StreamReader(filein)
                Dim sout As New IO.StreamWriter(fileout, False)
                sout.AutoFlush = False
                Dim lineCounter As Integer
                Dim items() As String
                Dim readline As String = sin.ReadLine
                While Not IsNothing(readline)
                    items = readline.Split(vbTab)
                    items(3) = items(3) & "," & IPLookup(items(3))
                    items(5) = items(5) & "," & IPLookup(items(5))
                    sout.WriteLine(String.Join(",", items))
                    lineCounter = lineCounter + 1
                    If lineCounter Mod 50 = 0 Then
                        sout.Flush()
                    End If
                    readline = sin.ReadLine
                End While
                sin.Close()
                sout.Flush()
                sout.Close()
            End If
        Catch ex As Exception
            MsgBox("Problem Occurred" & ex.Message)
        End Try
    End Sub


    Public Function Dot2LongIP(ByVal DottedIP As String) As Long
        Dim arrDec() As String
        Dim i As Integer
        Dim intResult As Long
        If DottedIP = "" Then
            Return 0
        Else
            arrDec = DottedIP.Split(".")
            For i = 0 To 2
                intResult = intResult + (Int(arrDec(i)) * Math.Pow(256, 3 - i))
            Next
            Return intResult
        End If
    End Function

    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 'problem area
        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 = "Not in Database"
        Else
            strCountry = dt.Rows(0).Item("countryname")
        End If

        Return strCountry
    End Function

End Class


gr8lifeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SanclerConnect With a Mentor Commented:
Chaosian's code addresses the connection problem.  But I think that, for overall speed purposes, it might benefit from tweaking a bit.

Using DataTable.Select is, I think, likely to be faster than using a DataView with .RowFilter.  I've just done some tests with (roughly) equivalent data and I was getting a 25% reduction in turn-around time.

Much more importantly, however, the declaration of a new object to receive the results of the filter test, whether that was a dataview or an array of datarows, introduces an overhead.  The slowdown of doing it within the sub was not much with a datarow array and DataTable.Select, but it was by a factor of about 4 with DataView and .RowFilter.  So I reckon that declaration ought to be outside, but with scope for, this sub - which is called repeatedly.

Finally - and (a) I'm not sure about this and (b) any difference seemed in my tests to be marginal - I wonder whether it's necessary to convert the lngIP .ToString before building the filter?  My tests worked OK without doing that, but perhaps my datatypes were different.

Putting that together, I would modify the first part of Chaosian's code as follows

Dim _ipData as DataTable
Dim dr As DataRow '<<< NEW LINE
Public Function IPLookup(ByVal DottedIP As String) As String
If _ipData Is Nothing then GetIpData()
  Dim lngIP As Long = Dot2LongIP(DottedIP)
  'Dim dv As New DataView(_ipData) '<<< COMMENTED OUT
  dr = _ipData.Select(lngIP & " >= ipL1 And " & lngIP & " <= ipL2") '<<< NEW LINE, REPLACING ...
  'dv.RowFilter = lngIP.ToString & " >= ipl1 and " & lngIP.ToString & "  <= ipl2"
  If dr.Length = 0 Then '<<< NEW LINE, REPLACING ...
  'If dv.Count = 0 Then
  'ETC

Roger
0
 
Jeff CertainCommented:
1. If you're only targeting Access databases, you don't need to import (or reference) System.Data.Common. This namespace is only required if you are attempting database agnosticism. (If you don't know what that is, you ain't doing it!)

2. In general, Access is only really suitable for a single-user environment. Having said that, you can probably bring data in without having the connecition open and close very often.

3. conn.Open: da.Fill: conn.Close contains two lines of code too many -- da.Fill handles opening and closing the database connection for you.

4. You don't need table mappings :)

The simplified version:

 Public Function IPLookup(ByVal DottedIP As String) As String
        Dim lngIP As Long = Dot2LongIP(DottedIP)
        Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\resource\master.mdb")
        Dim cmdSelect As New OleDbCommand("SELECT countryname FROM country WHERE " & lngIP.ToString & " >= ipl1 and "&  _
                   lngIP.ToString & "  <= ipl2", con)
Dim da As New OleDbDataAdapter(cmdSelect)
Dim dt As New DataTable
        da.Fill(dt)
        If dt.Rows.Count = 0 Then
            Return "Not in Database"
        Else
            Return dt.Rows(0).Item("countryname")
        End If
    End Function
0
 
Jeff CertainConnect With a Mentor Commented:
Okay... now for the intelligent approach :)

Dim _ipData as DataTable
Public Function IPLookup(ByVal DottedIP As String) As String
If _ipData Is Nothing then GetIpData()
  Dim lngIP As Long = Dot2LongIP(DottedIP)
  Dim dv As New DataView(_ipData)
  dv.RowFilter = lngIP.ToString & " >= ipl1 and " & lngIP.ToString & "  <= ipl2"
  If dv.Count = 0 Then
    Return "Not in Database"
  Else
    Return dv(0)("countryname")
  End If
End Function

Private Sub GetIpData()
  Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\resource\master.mdb")
  Dim cmdSelect As New OleDbCommand("SELECT countryname, ipl1, ipl2 FROM country", con)
  Dim da As New OleDbDataAdapter(cmdSelect)
  _ipData = New DataTable
  da.Fill(_ipData)
End Sub
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Jeff CertainCommented:
The above code will read your data in once, then simply query against it once it is all in memory. This will keep your database connection count down to exactly 1.
0
 
SanclerCommented:
ERROR!

Dim dr As DataRow '<<< NEW LINE

should read

Dim dr As DataRow() '<<< NEW LINE

and I see I put ipL1 instead of ipl1

Roger
0
 
gr8lifeAuthor Commented:
Thank you very much for the desperately needed expert advice.
Gr8life
0
All Courses

From novice to tech pro — start learning today.