RE: gridview databind timeout

Thanks. Answers follow. Please let me know if you need more data. The
table this is hitting has over 100,000 rows (no problem with it when it is
under 20,000). The wierd part is that first time through the code it works
fine (comes back in about 3 seconds), but times out the second time through
with a much smaller result set.

Timeout is the default value (30 seconds I believe).

Times out on Me.GridView1.DataBind() in the Page_LoadComplete event.

Text of the function that contains the DataBind is:

Protected Sub Page_LoadComplete(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.LoadComplete

'This gets the filter from the Master page for the alpha buttons
If Trim(Me.txtQry3.Text) = "" Then Me.txtLastName.Text =
CType(Master.FindControl("txtSelectFilter"), TextBox).Text
If Trim(Me.txtQry4.Text) = "" Then Me.txtOrganizationName.Text =
CType(Master.FindControl("txtSelectFilter"), TextBox).Text

Try
Me.GridView1.DataBind()
If Me.GridView1.Rows.Count < 1 Then
CType(Master.FindControl("pnlDetailResults"), Panel).Visible
= False
Else
Me.FormView1.DataBind()
End If
SetSubjectID()

Catch ex As Exception
lblError.Text = ex.Message.ToString
End Try

End Sub



The Stored Procedure (with some of the encryption keys removed) is:

ALTER PROCEDURE [dbo].[ssp_SearchSubjects_Get_Subjects]
@SubjectType INT, -- 0 for all, 1 for persons, 2 for organizations
@FirstName NVARCHAR(128),
@LastName NVARCHAR(128),
@MiddleName NVARCHAR(128),
@OrganizationName NVARCHAR(128),
@Address1 NVARCHAR(128),
@Address2 NVARCHAR(128),
@City NVARCHAR(128),
@State_Province NVARCHAR(128),
@PostalCode NVARCHAR(128),
@CountryID INT -- 0 returns all Countries
AS

OPEN SYMMETRIC KEY xxxxx
DECRYPTION BY CERTIFICATE xxxxx;

-- Note the views (vw_xxxx) decrypt the encrypted values, for example
ISNULL(CONVERT (nvarchar(50), DecryptByKey(EncryptedFirstName)), N'')

SELECT
dbo.SearchSubject.SearchSubjectID,
dbo.vw_PersonName.LastName + ', ' + dbo.vw_PersonName.FirstName + ' ' +
isnull(dbo.vw_PersonName.MiddleName, '') AS Name,
ISNULL(dbo.vw_IDNum.IDNumber, 'NONE') AS IDNum,
'Person' AS Type,
isnull(dbo.vw_Address.Address1, '') AS Address1,
isnull(dbo.vw_Address.Address2, '') AS Address2,
isnull(dbo.vw_Address.City, '') AS City,
isnull(dbo.vw_Address.State_Province, '') AS State_Province,
isnull(dbo.vw_Address.PostalCode, '') AS PostalCode,
isnull(dbo.vw_Address.CountryID, 0) AS CountryID,
0 as JobStatusID
FROM dbo.SearchSubject INNER JOIN
dbo.vw_Person ON dbo.SearchSubject.PersonID = dbo.vw_Person.PersonID INNER
JOIN
dbo.vw_PersonName ON dbo.vw_PersonName.PersonID = dbo.vw_Person.PersonID
LEFT JOIN
dbo.vw_IDNum ON dbo.vw_IDNum.PersonID = dbo.vw_Person.PersonID LEFT JOIN
dbo.Person_mm_Address ON dbo.Person_mm_Address.PersonID =
dbo.vw_Person.PersonID LEFT JOIN
dbo.vw_Address ON dbo.Person_mm_Address.AddressID = dbo.vw_Address.AddressID
WHERE ISNULL(dbo.vw_PersonName.LastName, '') LIKE @LastName AND
ISNULL(dbo.vw_PersonName.FirstName, '') LIKE @FirstName AND
ISNULL(dbo.vw_PersonName.MiddleName, '') LIKE @MiddleName AND
ISNULL(dbo.vw_Address.Address1, '') LIKE @Address1 AND
ISNULL(dbo.vw_Address.Address2, '') LIKE @Address2 AND
ISNULL(dbo.vw_Address.City, '') LIKE @City AND
ISNULL(dbo.vw_Address.State_Province, '') LIKE @State_Province AND
ISNULL(dbo.vw_Address.PostalCode, '') LIKE @PostalCode
ORDER BY 2

CLOSE SYMMETRIC KEY xxxxx





"Kevin Yu [MSFT]" wrote:

Hi,

I'd like to query some information on this issue for a better research. How
long have you set the timeout value to? And after how long did the timeout
exception thrown? Please step into the code to see which line blocks the
execution.

Also could you show me some of your code and the stored procedure?

Thanks!

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)


.