Solved

Limit opening and closing of the database connection

Posted on 2006-07-11
6
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

626 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