gridview databind timeout

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an application with a GridView with a data source of a stored
procedure in MS SQL Server 2005.

The stored procedure is going against a large encrypted table, so is memory
and processing intensive. However, the stored procedure does run in an
acceptable time (about 2 to 3 seconds). I can run the stored procedure back
to back in a query with no degradation of performance. The stored procedure
takes some search parameters.

However, when I run it in vb.net, the datagrid loads fine on the first load,
but if I change a search criteria and reload it timeouts, even though the
result set in the second instance is smaller than the first instance (timeout
is on the gridview databind function).

My question is, how do I get around this? Apparently memory is getting used
and not released or something, but so much of the gridview is hidden in
version 2, I don't see a way to clear it, or close and reopen a connection or
any such. I've tried limiting the result set to as low as 5 rows (using top
in the stored procedure), but that makes no difference. If I cut the table
size the stored procedure runs against way down though, it all runs fine.
Apparently, going through .net to the stored procedure trashes memory, which
doesn't happen when the stored procedure is run in a query window in sql
server.

Can you advise me on this?

Thanks.
 
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.)
 
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
 
There is a further piece of data I found testing. Not only does it time out
on the databind, a simple action like clicking the + sign on a navigation
tree takes 30 seconds after the initial load (normally instantaneous). It
all works fine up to 80000 records, but at 100000 it goes screwy. Even if I
change the proc and return only the top 5 rows... Am I simply running into a
memory barrier with all the encryption that requires swapping out to the hard
disk or something?

Gerhard said:
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 said:
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.)
Click to expand...
Click to expand...
 
Further data: If I take the asp:LoginName, asp:SiteMapPath out of the master
page, and run using Windows authentication, it runs fine with over 100000
rows. This is very puzzling.

Gerhard said:
There is a further piece of data I found testing. Not only does it time out
on the databind, a simple action like clicking the + sign on a navigation
tree takes 30 seconds after the initial load (normally instantaneous). It
all works fine up to 80000 records, but at 100000 it goes screwy. Even if I
change the proc and return only the top 5 rows... Am I simply running into a
memory barrier with all the encryption that requires swapping out to the hard
disk or something?

Gerhard said:
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 said:
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.)
Click to expand...
Click to expand...
Click to expand...
 
Hi Gerhard,

In this case, I suspect it is not the query that blocks the whole loading
process. To confirm this, I suggest you start a SQL trace using the SQL
Profiler. It will get the duration of each SQL query.

I think there might be the network problem or some resource problem here.
Is the sitemap querying some external resource like pictures?

Kevin Yu
Microsoft Online Community Support
==================================================

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

What is apparently killing it, from the SQL Trace, is:

Audit Logout Report Server SYSTEM NT
AUTHORITY\SYSTEM 0 52 0 190233 744 51 2006-09-19 08:35:38.373 2006-09-19
08:38:48.607

This is probably why it started running at 100,000+ when I changed to
Windows Authentication.

The sitemap is not going against any pictures, just a web.sitmap file.

First, I don't know why there are calls to Report Server. Is this
necessary, or can it be turned off somehow? Or can at least the Audit Logout
be turned off or somehow sped up?

Thanks.
 
An interesting point, when the table is smaller, it never hits the Audit
Logout in the SQL Trace. So, apparently, the large table size is forcing a
logout for some reason... Any ideas how to fix this other than buying a
bigger computer or adding memory (note the same problem occurs on a XP Pro
box and a Windows 2003 Server box)?
 
Some further data, I got this set up on what will be the production box, 4
gig of memory (as opposed to the 1 gig on the earlier machines), 2 physical
processors with two cores each, so essentially 4 processors, etc., and it
does the same thing. There is some resource hogging going on that isn't
being released apparently. I do know I can handle the full table if I don't
use Forms Authentication (use Windows Authentication instead), but that is
really not an option.

Help sorting this out would really be appreciated, as we are getting close
to go live date.
 
Hi Gerhard,

The Audit Logout event from Report Server is fired when a client of
reporting service is closed or disconnected from the service. I could not
see any relationship between this server and your query stored procedure.
Is there any other people who is utilizing the reporting service on this
machine?

Please try to mitigate all the impacts from other clients and trace again.
Could you send me the trace log by email? Remove 'online' from the no spam
alias is my real email. Thank you!

Kevin Yu
Microsoft Online Community Support
==================================================

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

Have you finished with the trace log? I would like to get some more
information about the timeout exception. Could you post the exact timeout
exception message here? If you don't mind, it will be good to post the
whole error page with stack trace here. Thank you!

Kevin Yu
Microsoft Online Community Support
==================================================

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