0

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.

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by Oxiegen
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.