asked on

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:

https://www.experts-exchange.com/questions/21784599/Performance-Improvements.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

Jeff Certain

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

SOLUTION

Jeff Certain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeff Certain

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.

ASKER CERTIFIED SOLUTION

Sancler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sancler

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

gr8life

ASKER

Thank you very much for the desperately needed expert advice.
Gr8life