Please Help me improve this SPROC!!! It runs too slow!

[perryg30313]

I have a vb .NET 2005 form that needs to load data based on a selected month by the user. Because there is a large amount of data in the table (60k+ records per month) it is taking the query a long time to pull the dataset back and populate the form. Can anyone give me some suggestions on how I can decrease the overhead associated with querying a large dataset to populate a base form?

Note, I have created an identity key on the main table but haven't yet indexed any other fields (Though I could use some suggestions on how I should index).

Please see the following example of the Stored Proc that is run before my .net form is launched:

Dim cmd2 As New SqlCommand
Dim ConnectionString As String
Dim TheDatabase As System.Data.SqlClient.SqlConnection


ConnectionString = "Data Source=90.152.60.72;Initial Catalog=Adjustments;User ID=Adjust;Password=adjustments"
TheDatabase = New SqlClient.SqlConnection(ConnectionString)


cmd2.Connection = TheDatabase
cmd2.CommandText = "spGetSalesCode"
cmd2.CommandType = CommandType.StoredProcedure
cmd2.Parameters.AddWithValue("@Period", "2010-06-01")
Dim da2 As New SqlDataAdapter(cmd2)

da2.Fill(dsSalesCode, "Init Sales Code")
da2.Fill(dsSalesCode, "Disp Sales Code")


        'Fill the Sales Code Combo Box with values from my test date 6-1-2010
        ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
        With ComboBox1
            .DataSource = dsSalesCode.Tables("Init Sales Code")
            .DisplayMember = "Sales Code"
            .SelectedIndex = 0
        End With

        'Fill the Sales Code Combo Box with values from my test date 6-1-2010
        ComboBox2.DropDownStyle = ComboBoxStyle.DropDownList
        With ComboBox2
            .DataSource = dsSalesCode.Tables("Disp Sales Code")
            .DisplayMember = "Sales Code"
            .SelectedIndex = 0
        End With

Here is a copy of the Sproc:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
-- =============================================
-- Author:		<Author,,Name>
-- ALTER  date: <ALTER  Date,,>
-- Description:	<Description,,>
-- =============================================
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], UPPER(VP_ATTUID) AS VP_ATTUID, UPPER(GM_ATTUID) AS GM_ATTUID, 
		UPPER(SVP_ATTUID) AS SVP_ATTUID, UPPER(SM_ATTUID) AS SM_ATTUID, UPPER(COACH_ATTUID) AS COACH_ATTUID, 
		UPPER(REP_ATTUID) AS REP_ATTUID
	FROM dbo.tHeirarchyData
	WHERE PERIOD = @Period AND [SALES CODE] IS NOT NULL
	ORDER BY [SALES CODE]
END

Here is the Table Structure

CREATE TABLE [tHierarchy] (
	[Id] [uniqueidentifier] NOT NULL ,
	[SummaryMonth] [smalldatetime] NOT NULL ,
	[Level1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level5] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TypeCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FullName] AS ([FirstName] + ' ' + [LastName]) ,
	[ATTUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_tHierarchy] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO

As you can see, I am new to vb.net programming and could use any criticism's and suggestions possible.

Thanks in advance for help in ways of improving my code.

Greg

[PsychoCoder]

If you're pulling back 60k+ records per query there's really nothing you're going to do to make it faster within the stored procedure. A server can only do so much so fast. Now you can make it not appear your UI is freezing if you were to use something like a BackgroundWorker to do your processing in.

Going this route will at least let the UI be usable while the data is being returned.

[perryg30313]

If you're pulling back 60k+ records per query there's really nothing you're going to do to make it faster within the stored procedure. A server can only do so much so fast. Now you can make it not appear your UI is freezing if you were to use something like a BackgroundWorker to do your processing in.

Going this route will at least let the UI be usable while the data is being returned.

This is a GREAT suggestions. Thank you so much, I will be able to use this I think in my processing. I'm also going to have to restructure the entire table that I'm using to store the data and normalize to improve performance.

Thanks Again for answering this post!!

[perryg30313]

I ended up having to restructure the table that this SPROC was pulling from because of the large amound of data and lack of indexes it was causing very low response times from the database (Like 18 seconds to get a recordset when a button is pressed), by re-doing the table structure and removing fields that I didn't need, normalizing the ones I did, I was able to cut my response from 18 seconds to 1 second.

Thanks for your suggestions.