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"

ConnectionString = "Data Source=;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"

End Sub

I'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:

8 Years
Discussion Span
Last Post by P-Geist

Personally you should try and split it into seperate functions/subs depending on the result they return.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.