I've got a vb.net 2005 form that calls a stored proc to load a combobox with eligible sales reps. There are approximately 40k sales reps available...I know that's a lot, but that's what I'm dealing with. I'm trying to figure out why it's taking this form 15 seconds to load.
If anyone can offer suggestions on how I can better to this I wold appreciate!!
Here's my code:
Private Sub frmNewAdjustment_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim f As New frmAdjustments
Dim ConnectionString As String
Dim dsSalesCode As DataSet = New DataSet
Dim cmd As New SqlCommand
Dim cmd2 As New SqlCommand
Dim cmd3 As New SqlCommand
MonthVar = "2010-08-01"
txtOASalesCd.Focus()
ConnectionString = "Data Source=90.152.60.72;Initial Catalog=Adjustments;User ID=Adjust;Password=adjustments"
TheDatabase = New SqlClient.SqlConnection(ConnectionString)
cmd.Connection = TheDatabase
cmd.CommandText = "SELECT DISTINCT SummaryMonth FROM dbo.tSalesCodes ORDER BY SummaryMonth DESC"
cmd.CommandType = CommandType.Text
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "Summary Month")
cmd2.Connection = TheDatabase
cmd2.CommandText = "spGetSalesCode"
cmd2.CommandType = CommandType.StoredProcedure
cmd2.Parameters.AddWithValue("@Period", MonthVar)
Dim da2 As New SqlDataAdapter(cmd2)
da2.Fill(dsSalesCode, "Init Sales Code")
da2.Fill(dsSalesCode, "Disp Sales Code")
cboPeriod.DropDownStyle = ComboBoxStyle.DropDownList
With cboPeriod
.DataSource = ds.Tables("Summary Month")
.DisplayMember = "SummaryMonth"
.SelectedIndex = 0
End With
'Fill the Sales Code Combo Box with values from my test date 6-1-2010
ComboBox1.AutoCompleteMode = AutoCompleteMode.Append
ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
ComboBox1.AutoCompleteSource = AutoCompleteSource.ListItems
With ComboBox1
.DataSource = dsSalesCode.Tables("Init Sales Code")
.DisplayMember = "Sales_Code"
.SelectedIndex = 0
End With
txtOASalesCd.DataBindings.Add("Text", dsSalesCode.Tables("Init Sales Code"), "Sales_Code")
txtInitRep.DataBindings.Add("Text", dsSalesCode.Tables("Init Sales Code"), "REP_ATTUID")
txtInitCoach.DataBindings.Add("Text", dsSalesCode.Tables("Init Sales Code"), "COACH_ATTUID")
txtInitCL.DataBindings.Add("Text", dsSalesCode.Tables("Init Sales Code"), "SM_ATTUID")
txtInitAVP.DataBindings.Add("Text", dsSalesCode.Tables("Init Sales Code"), "GM_ATTUID")
txtInitVP.DataBindings.Add("Text", dsSalesCode.Tables("Init Sales Code"), "VP_ATTUID")
'Fill the Sales Code Combo Box with values from my test date 6-1-2010
ComboBox2.AutoCompleteMode = AutoCompleteMode.Append
ComboBox2.DropDownStyle = ComboBoxStyle.DropDownList
ComboBox2.AutoCompleteSource = AutoCompleteSource.ListItems
With ComboBox2
.DataSource = dsSalesCode.Tables("Disp Sales Code")
.DisplayMember = "Sales_Code"
.SelectedIndex = 0
End With
txtDispRep.DataBindings.Add("Text", dsSalesCode.Tables("Disp Sales Code"), "REP_ATTUID")
txtDispCoach.DataBindings.Add("Text", dsSalesCode.Tables("Disp Sales Code"), "COACH_ATTUID")
txtDispCL.DataBindings.Add("Text", dsSalesCode.Tables("Disp Sales Code"), "SM_ATTUID")
txtDispAVP.DataBindings.Add("Text", dsSalesCode.Tables("Disp Sales Code"), "GM_ATTUID")
txtDispVP.DataBindings.Add("Text", dsSalesCode.Tables("Disp Sales Code"), "VP_ATTUID")
cmd3.Connection = TheDatabase
cmd3.CommandText = "SELECT Distinct USOC, ID FROM dbo.[tUsoc] WHERE USOC IS NOT NULL ORDER BY USOC"
cmd3.CommandType = CommandType.Text
Dim da3 As New SqlDataAdapter(cmd3)
da3.Fill(ds, "Product Name")
Dim NewColumn As New DataGridViewComboBoxColumn
Dim NewColumn2 As New DataGridViewTextBoxColumn
Dim NewColumn3 As New DataGridViewTextBoxColumn
NewColumn.Name = "USOC"
NewColumn2.Name = "Order Detail ID"
NewColumn.DataSource = ds.Tables("Product Name")
NewColumn.DisplayMember = "USOC"
NewColumn.ValueMember = "ID"
dgAdjustment.Columns.Add(NewColumn)
dgAdjustment.Columns.Add(NewColumn2)
End SubI'm new to this sort of coding and want to load this in the most efficient way possible given the HUGE amount of data that my client is wanting to see.
Can you help me? :confused:
