Slow first connection to SQL server in VB.NET

Posted on 2005-04-11
Last Modified: 2008-02-01
I have a form with a textbox and listbox on it, it is designed for the user to enter name then press enter and it will display records that match.

sqlClient.SelectCommand.CommandText = "SELECT ClientID, Name FROM tblClient WHERE Name LIKE '%" & Replace(replace(txtSearch.Text,"'","''"), " ", "%") & "%'"

The wierd thing is that the first time this is run it takes about 10-15 seconds to come back with the results, then for every other attempt while the program is open it is lightning just under the 1sec mark (which is more like it), if you shut the program down and try it again it will be slow for that first attempt again.

I have tried to trick the SQL connection by when the program starts up I get it to
txtSQLVersion.Text = SQL.sqlBMS.ServerVersion()

I thought that at least then the user waits 10 seconds while the program loads instead of the first time they go to access a this search window.

Any thoughts
Question by:delta-mp
    LVL 27

    Expert Comment

    Here is some sample code that might give you an idea on connection and hitting the database.
    Just change the module and form to fit your needs and test....

    'FORM 1

    Imports System.Data
    Imports System.Data.SqlClient
    Public Class frmSQL_ADO
        Inherits System.Windows.Forms.Form

    #Region " Windows Form Designer generated code "

        Public Sub New()

            'This call is required by the Windows Form Designer.

            'Add any initialization after the InitializeComponent() call

        End Sub

        'Form overrides dispose to clean up the component list.
        Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If Not (components Is Nothing) Then
                End If
            End If
        End Sub

        'Required by the Windows Form Designer
        Private components As System.ComponentModel.IContainer

        'NOTE: The following procedure is required by the Windows Form Designer
        'It can be modified using the Windows Form Designer.  
        'Do not modify it using the code editor.
        Friend WithEvents ListView1 As System.Windows.Forms.ListView
        Friend WithEvents Button1 As System.Windows.Forms.Button
        Friend WithEvents Button2 As System.Windows.Forms.Button
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
            Me.ListView1 = New System.Windows.Forms.ListView
            Me.Button1 = New System.Windows.Forms.Button
            Me.Button2 = New System.Windows.Forms.Button
            Me.ListView1.Location = New System.Drawing.Point(16, 8)
            Me.ListView1.Name = "ListView1"
            Me.ListView1.Size = New System.Drawing.Size(372, 140)
            Me.ListView1.TabIndex = 0
            Me.ListView1.View = System.Windows.Forms.View.Details
            Me.Button1.Location = New System.Drawing.Point(20, 172)
            Me.Button1.Name = "Button1"
            Me.Button1.TabIndex = 1
            Me.Button1.Text = "GO"
            Me.Button2.Location = New System.Drawing.Point(140, 172)
            Me.Button2.Name = "Button2"
            Me.Button2.TabIndex = 2
            Me.Button2.Text = "Exit"
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(400, 205)
            Me.Name = "frmSQL_ADO"
            Me.Text = "frmSQL_ADO"

        End Sub

    #End Region
        '/ <summary>
        '/ The main entry point for the application.
        '/ </summary>
        <STAThread()> Public Shared Sub Main()
            Application.Run(New frmSQL_ADO)
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        End Sub
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        End Sub
        Private Sub InitializeData()
            Cursor.Current = Cursors.WaitCursor


            Sql = ""
            Sql = "SELECT * "
            Sql += "FROM " & sSQLTableName & " "
            Sql += "ORDER BY ProductName"

                myDataAdapter = New SqlDataAdapter(Sql, Connect)

            Catch Exp As Data.SqlClient.SqlException
                MsgBox("FillDataSet Procedure Error", MsgBoxStyle.Critical, "Load DataAdapter Error")
            Catch Exp As Exception
                MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
            End Try
            Cursor.Current = Cursors.Default
        End Sub
        Private Sub InitializeListView()
            With ListView1
                .Columns.Add("Product Name", -1, HorizontalAlignment.Right)
                .Columns.Add("Unit Price", -2, HorizontalAlignment.Left)
                .Columns.Add("Units In Stock", 100, HorizontalAlignment.Right)
            End With
        End Sub
        Private Sub PopulateListFromDS(ByVal DS As DataSet)
            Dim i As Integer


            For Each mySQLDataRow In DS.Tables(0).Rows
                With ListView1
                    i += 1
                End With
            ListView1.Sorting = SortOrder.Ascending
            ListView1.View = View.Details

        End Sub

    End Class


    Imports System.Data.SqlClient

    Module ModSQL
    #Region " MY Global Vars "
        '********* database Var's ************
        Public Sql As String
        Public mySQLDataRow As DataRow
        Public myDS As New DataSet
        Public myDataAdapter As SqlDataAdapter      '(SQL)

        '**** Server Connection Vars *********
        Public sSQLDBServer As String = "(local)\SQLDESKTOP"   '<--- Server Name on desktop MSDE
        Public sSQLDBName As String = "Northwind"              '<--- Database Name
        Public sSQLTableName As String = "Products"            '<--- Table Name
        Public Connect As SqlConnection                        '<--- for msSQL

        '******* Standard Var's *************
        Public sPath As String = Application.StartupPath

    #End Region
    #Region " MY Server Connections "
        Public Sub OpenDBSqlConnection()
                Connect = New SqlConnection("DataBase=" & sSQLDBName & ";Server=" & sSQLDBServer & ";Integrated Security=SSPI")
            Catch Ex As System.NullReferenceException
                MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
                Cursor.Current = Cursors.Default
            Catch Ex As Exception
                MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
                Cursor.Current = Cursors.Default
            End Try
        End Sub
    #End Region
    End Module
    LVL 5

    Expert Comment

    It could be a couple things:

    1) Depending on the query your parameters are not yet cached on the sever.

    2) The .NET framework actually pools connections and the first time you run your app the connection is prolly not cached either.
    LVL 5

    Accepted Solution


    Author Comment

    It did help explain how the .NET framework handles database connections and it's caching process, I have accepted that it must indeed be a microsoft thing.

    Thanks all for your assistance. and apologises for not getting back to you guys sooner, gotta get back to checking up here more often.


    Michael Proctor

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of convertingā€¦
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    732 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

    21 Experts available now in Live!

    Get 1:1 Help Now