Context: migrating from one environment to another, in old environment when loading the ASP.NET page that used this stored procedure, it ran quickly, now it's a 20 second wait until the same page loads. Ran SQL Server Profiler while trying to load the page, as soon as page finishes loading I see the stored procedure in profiler and it says it says duration is around 20 seconds, which corresponds to the page load time. When I execute the stored procedure in a SSMS window, it returns nearly instantly, so I'm not sure why the difference...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[GetBusinessesByOfficeAndLetter] (
@workunit int,
@letter varchar(5)
) AS
SELECT
[BusinessActivity].[ident], [fullname], [addr1], [city], [state],
[status], ProductType.name, lastactivity, openactivities
FROM
[BusinessActivity]
INNER JOIN
Workunit ON BusinessActivity.workunit = Workunit.ident
LEFT JOIN
workunit area ON Workunit.parent = area.ident
LEFT JOIN
workunit region ON area.parent = region.ident
LEFT OUTER JOIN
ProductType ON BusinessActivity.productdem1 = ProductType.ident
LEFT OUTER JOIN
RiskLevel ON BusinessActivity.risklevel = RiskLevel.abbr
WHERE (Workunit.ident=@workunit OR Area.ident=@workunit OR region.ident=@workunit) AND fullname LIKE @letter+'%' ORDER BY [fullname], [city]
BusinessActivity is a view, and the last two items in the select query of this stored procedure are computed columns in that view, but I can't imagine that's an issue.
Calling Code:
Protected Sub GetBusinessListData(ByVal sender As Object, ByVal e As System.EventArgs)
Try
Dim daLoc As SqlDataAdapter = New SqlDataAdapter()
Dim dsLoc As New Data.DataSet
Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
Dim cmdX As SqlCommand = cn.CreateCommand
cmdX.CommandText = "GetBusinessesByOfficeAndLetter"
cmdX.CommandType = Data.CommandType.StoredProcedure
cmdX.Parameters.AddWithValue("workunit", Me.cboWorkunit.SelectedValue)
If hdnLetter.Value = "" Then hdnLetter.Value = "XXXX"
cmdX.Parameters.AddWithValue("letter", Me.hdnLetter.Value)
daLoc.SelectCommand = cmdX
daLoc.Fill(dsLoc)
Me.gvLocations.DataSource = dsLoc.Tables(0)
Session("LocList") = dsLoc.Tables(0)
Me.gvLocations.DataBind()
Me.gvLocations.SelectedIndex = -1
Catch ex As Exception
Dim p As String = ex.Message
End Try
End Sub
