Link to home
Start Free TrialLog in
Avatar of delta-mp
delta-mp

asked on

Slow first connection to SQL server in VB.NET

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,"'","''"), " ", "%") & "%'"
sqlClient.Fill(DsClient)

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
SQL.sqlBMS.Open()
txtSQLVersion.Text = SQL.sqlBMS.ServerVersion()
SQL.sqlBMS.Close()

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
Avatar of Howard Cantrell
Howard Cantrell
Flag of United States of America image

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()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        '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
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    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.SuspendLayout()
        '
        'ListView1
        '
        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
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(20, 172)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "GO"
        '
        'Button2
        '
        Me.Button2.Location = New System.Drawing.Point(140, 172)
        Me.Button2.Name = "Button2"
        Me.Button2.TabIndex = 2
        Me.Button2.Text = "Exit"
        '
        'frmSQL_ADO
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(400, 205)
        Me.Controls.Add(Me.Button2)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.ListView1)
        Me.Name = "frmSQL_ADO"
        Me.Text = "frmSQL_ADO"
        Me.ResumeLayout(False)

    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
        InitializeData()
        InitializeListView()
        PopulateListFromDS(myDS)
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub
    Private Sub InitializeData()
        Cursor.Current = Cursors.WaitCursor

        OpenDBSqlConnection()

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

        Try
            myDataAdapter = New SqlDataAdapter(Sql, Connect)
            myDataAdapter.Fill(myDS)

        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

        ListView1.Items.Clear()

        For Each mySQLDataRow In DS.Tables(0).Rows
            With ListView1
                .Items.Add(mySQLDataRow("ProductName").ToString)
                .Items(i).SubItems.Add(mySQLDataRow("UnitPrice").ToString)
                .Items(i).SubItems.Add(mySQLDataRow("UnitsInStock").ToString)
                i += 1
            End With
        Next
        ListView1.Sorting = SortOrder.Ascending
        ListView1.View = View.Details

    End Sub

End Class


'MODULE

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()
        Try
            Connect = New SqlConnection("DataBase=" & sSQLDBName & ";Server=" & sSQLDBServer & ";Integrated Security=SSPI")
            Connect.Open()
            Connect.Close()
        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
Avatar of thenrich
thenrich

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.
ASKER CERTIFIED SOLUTION
Avatar of thenrich
thenrich

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of delta-mp

ASKER

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.

Regards,

Michael Proctor