I am using a stored procedure to fill a dataset with a large amount of data (47k rows and 8 columns). I am needing to fill two tables with the results of this stored proc.
One table will have data representing 47k reps that currently have credit for a transaction. The other table will be a mirror copy of the 1st table with 47k reps that were supposed to get credit for a transaction. This application is going to take credit from one rep. and give the credit to the other rep record in the 2 mirrored datasets. It's taking 30 seconds for the data adapter fill method to return records to my application. I would like to reduce the amount of time it takes for this request to process.
Here's the contents of the stored proc:
ALTER PROCEDURE [spGetSalesCode] @Period varchar(15) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT Sales_Code, R.RepATTUID AS REP_ATTUID, COACH_ATTUID, SM_ATTUID, GM_ATTUID, VP_ATTUID, '' AS SVP_ATTUID FROM tSalesCodes S INNER JOIN tRepTable R ON S.RepATTUID = R.RepATTUID AND S.SummaryMonth = R.SummaryMonth INNER JOIN tHierarchy ON R.RepATTUID = tHierarchy.Rep_ATTUID AND S.SummaryMonth = tHierarchy.SummaryMonth WHERE s.SummaryMonth = @Period ORDER BY Sales_Code END
Here's the call from vb.net
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") With ComboBox1 .DataSource = dsSalesCode.Tables("Init Sales Code") .DisplayMember = "Sales_Code" .SelectedIndex = 0 End With ComboBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend ComboBox1.AutoCompleteSource = AutoCompleteSource.ListItems ComboBox1.DropDownStyle = ComboBoxStyle.DropDown With ComboBox2 .DataSource = dsSalesCode.Tables("Disp Sales Code") .DisplayMember = "Sales_Code" .SelectedIndex = 0 End With ComboBox2.AutoCompleteMode = AutoCompleteMode.SuggestAppend ComboBox2.AutoCompleteSource = AutoCompleteSource.ListItems ComboBox2.DropDownStyle = ComboBoxStyle.DropDown
Does anyone have an idea on how I can
better return a relatively large dataset to
vb.net more quickly?
30 Seconds is unacceptable.