Solved

Limit opening and closing of the database connection

Posted on 2006-07-11
6
182 Views
Last Modified: 2010-08-05
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


0
Comment
Question by:gr8life
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17087046
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
 
LVL 24

Assisted Solution

by:Jeff Certain
Jeff Certain earned 250 total points
ID: 17087073
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
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17087078
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 34

Accepted Solution

by:
Sancler earned 250 total points
ID: 17089198
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
 
LVL 34

Expert Comment

by:Sancler
ID: 17089928
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
 

Author Comment

by:gr8life
ID: 17105139
Thank you very much for the desperately needed expert advice.
Gr8life
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now