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.Co mmandText = "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
sqlClient.SelectCommand.Co
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.ICon
'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.ListV
Friend WithEvents Button1 As System.Windows.Forms.Butto
Friend WithEvents Button2 As System.Windows.Forms.Butto
<System.Diagnostics.Debugg
Me.ListView1 = New System.Windows.Forms.ListV
Me.Button1 = New System.Windows.Forms.Butto
Me.Button2 = New System.Windows.Forms.Butto
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.
'
'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.ListVie
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.SqlExceptio
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("P
.Items(i).SubItems.Add(myS
.Items(i).SubItems.Add(myS
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.NullReferenceExcept
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