My project has a Search page and a Maintenance page. When the user enters criteria and clicks the Search button on the Search page, I'm using the code behind on the Search page to call a stored procedure which first determines if there is at least one record
that is returned. If yes, then I use a "Response.Redirect" passing url parameters to the Maintenance page. The Maintenance page has both a formview and a gridview control to display master/detail. The gridview shows the results of the search and the formview
shows the details of the record that is selected in the gridview. After the Search page logic is completed and a redirect is done, the PAGE_LOAD (of the Maintenance page) uses the url parameters that were passed to call the formview's datasource (stored procedure)
and binds. The same url parameters are used to call the gridview's datasource (another stored procedure) and binds.
For example, in the Search page's code behind:
protected void SearchButton_Click(object sender, EventArgs e)
{
if ((lstSearchBy1.SelectedValue == matterNumber) && (!String.IsNullOrEmpty(txtSearchValue1.Text)))
{
int return_type = sBLL.SearchCount_By_Matter_Number(txtSearchValue1.Text);
switch (return_type)
{
case 0: //Matter record found, send to Maintenance Page
{
Response.Redirect("Maintenance.aspx?searchtype=matter&matter=123456-666666");
}
case 1:
{
I display "No results found" message here.
}
}
}
}
In the code behind on the Maintenance page, the PAGE_LOAD does this when searching by a matter number:
Is this approach an acceptable way to utilize the asp.net controls/framework? There may be broad searches, that's why I am putting the results into a data table and putting the data table into the app cache (for paging/sorting). I would like to ask if there
is any way to improve performance for this type of search? If I run each stored procedure independently outside of the project, each runs between 4-6 seconds.
You're making 2 roundtrips to the database for the same record(s).
My suggestion would be to make 1 roundtrip to the database in the search screen, store the result in an xml file, and call your maintenance page via XMLHTTP object and pass the xml.
Why not still use a SqlDataSource for GridView. You can add QueryStringParameter into SqlDataSource to use values from url to select data from table. It may improve the performance. Furthermore, you can refer the way for efficient paging:
ChrisPalko
Member
68 Points
65 Posts
Possible Performance Improvement using Formview and Gridview?
Feb 23, 2012 03:13 PM|LINK
My project has a Search page and a Maintenance page. When the user enters criteria and clicks the Search button on the Search page, I'm using the code behind on the Search page to call a stored procedure which first determines if there is at least one record that is returned. If yes, then I use a "Response.Redirect" passing url parameters to the Maintenance page. The Maintenance page has both a formview and a gridview control to display master/detail. The gridview shows the results of the search and the formview shows the details of the record that is selected in the gridview. After the Search page logic is completed and a redirect is done, the PAGE_LOAD (of the Maintenance page) uses the url parameters that were passed to call the formview's datasource (stored procedure) and binds. The same url parameters are used to call the gridview's datasource (another stored procedure) and binds.
For example, in the Search page's code behind:
protected void SearchButton_Click(object sender, EventArgs e)
{
if ((lstSearchBy1.SelectedValue == matterNumber) && (!String.IsNullOrEmpty(txtSearchValue1.Text)))
{
int return_type = sBLL.SearchCount_By_Matter_Number(txtSearchValue1.Text);
switch (return_type)
{
case 0: //Matter record found, send to Maintenance Page
{
Response.Redirect("Maintenance.aspx?searchtype=matter&matter=123456-666666");
}
case 1:
{
I display "No results found" message here.
}
}
}
}
In the code behind on the Maintenance page, the PAGE_LOAD does this when searching by a matter number:
protected void Page_Load(object sender, EventArgs e)
{
try
{
FormView1DataSource.SelectParameters.Clear();
FormView1DataSource.SelectCommand = "sp_FMVWSearch_By_Matter_Number";
FormView1DataSource.SelectParameters.Add("Matter_Number", matter_nbr);
//Populate data table for gridview
dt = mBLL.spSearch_By_Matter_Number(matter_nbr);
dt.DefaultView.Sort = "matter_nbr ASC";
putResultsIntoAppCache(dt);
gvPracticeDetail.DataSource = dt;
gvPracticeDetail.DataBind();
gvPracticeDetail.SelectedIndex = 0;
}
}
This is what the formview's SqlDataSource mark-up looks like on the page:
<asp:SqlDataSource ID="FormView1DataSource" runat="server" ConnectionString="<%$ ConnectionStrings:PDMConnectionString %>"
OnSelecting="FormView1DataSource_OnSelecting" SelectCommandType="StoredProcedure" ></asp:SqlDataSource>
Is this approach an acceptable way to utilize the asp.net controls/framework? There may be broad searches, that's why I am putting the results into a data table and putting the data table into the app cache (for paging/sorting). I would like to ask if there is any way to improve performance for this type of search? If I run each stored procedure independently outside of the project, each runs between 4-6 seconds.
Thank you,
Chris
adamturner34
Contributor
3964 Points
999 Posts
Re: Possible Performance Improvement using Formview and Gridview?
Feb 23, 2012 03:27 PM|LINK
Well,
You're making 2 roundtrips to the database for the same record(s).
My suggestion would be to make 1 roundtrip to the database in the search screen, store the result in an xml file, and call your maintenance page via XMLHTTP object and pass the xml.
ChrisPalko
Member
68 Points
65 Posts
Re: Possible Performance Improvement using Formview and Gridview?
Feb 23, 2012 03:53 PM|LINK
Thank you for the quick reply. Could you provide me with a simplified example of how to do this using the structure I show in my post?
Thanks!
Chris
Qin Dian Tan...
All-Star
113532 Points
12480 Posts
Microsoft
Re: Possible Performance Improvement using Formview and Gridview?
Feb 27, 2012 07:32 AM|LINK
Hi,
Why not still use a SqlDataSource for GridView. You can add QueryStringParameter into SqlDataSource to use values from url to select data from table. It may improve the performance. Furthermore, you can refer the way for efficient paging:
http://weblogs.asp.net/lduveau/archive/2007/01/10/paging-tons-of-data-in-gridview.aspx
http://www.dotnetcurry.com/ShowArticle.aspx?ID=267
http://www.codeguru.com/csharp/.net/net_asp/controls/article.php/c15145
Thanks,
If you have any feedback about my replies, please contactmsdnmg@microsoft.com.
Microsoft One Code Framework