Hi, I have a SQLDatasource (sdsPlayerList) with lot's of data in it (it's list of all players available in league) and it's used with a dropdownlist in a gridview : gvLineUpTeam.
When my page is open, I guess that the SQLDatasource is filled with the list of players once at load ?
I wanted to know if the sdsPlayerList was kind of re-binded or reloaded at each postback cause a simple edit or update command in the page (in another gridView) is really long. I did this test : If I change my query in the StoredProc of my sdsPlayerList
to a "select top 30 ..." everything is super fast everywhere in my page).
How a edit/update/delete in another GridView (gvLineUpPenality) that has nothing to do with the sdsPlayerList make everything slow in page ?
Is there a way that the sdsPlayerList never get re-bind ou reload unless I do it in codebehind ?
All my gridview are inside different updatepanel (but not my SQLDataSource)
With your full list loaded in the dropdown, look at the source of the page in your browser. Each choice on that list is a line of html code. Therefore if you have a really long list, your page is bigger, hence slower. Anytime you have a huge dropdown
list this will be a problem.
Reporting by definition is different. Otherwise we would just show it on the screen.
When my page is open, I guess that the SQLDatasource is filled with the list of players once at load ?
Yes, when you bind the DataSource to the Dropdownlist, it will automatically fetch all the records from the real table accroding to your real SQL statement into the Dropdownlist as its elements.
marric01
I wanted to know if the sdsPlayerList was kind of re-binded or reloaded at each postback cause a simple edit or update command in the page (in another gridView) is really long. I did this test : If I change my query in the StoredProc of my sdsPlayerList to
a "select top 30 ..." everything is super fast everywhere in my page).
I don't suggest you using DataSource if your sql statement is always chaning dynamically.... I think you can do with a SqlDataAdapter.Fill into a DataTable, and then set the DataTextField and DataValueField and in the end call "DataBind()" method to the
Dropdownlist.
marric01
How a edit/update/delete in another GridView (gvLineUpPenality) that has nothing to do with the sdsPlayerList make everything slow in page ?
marric01
Is there a way that the sdsPlayerList never get re-bind ou reload unless I do it in codebehind ?
yes, just do data-binding directly in the if(!IsPostBack){.....}
My main goal if to find a way to improve performance of my page (It can be a little slow on first open).
Maybe I'm not explaining correcly what I'm trying to do and I'm pretty new to this programming and english is not my main language ;-).
So in my page, I have two gridview : One for visiting team and one for receving team. So those gridview are for building the line up of the game. (All my gridviews have there own seperate updatePanel)
I also have 4 other gridview for the production and penalties of players for each team (you can only add goal, assists and penalties if player were previously added in the line up, so the dropdownlist to select a player is pretty small)
Now, in my page I have created some sqldatasource to fill gridviews and dropdownlist inside gridviews. The sqldatasource wich cause (I think) the page to be very slow when performing operation that trigger postback is the sqldatasource that contains all
the players in the league (sdsListOfPlayers ). That sqldatasource is used inside the 2 line up gridview to fill the dropdownlist in <footerTemplate>, <editTemplate> and <emptyTemplate>.
So if i click edit command in a row in the lineup gridview, I have a dropdownlist and I can modify the players name. In the footerTemplate and emptyTemplate, I can
add a player in the line up from a dropdownlist with all the players name and there id's.
But the edit, insert and delete is very slow in all other gridviews even if theres nothing in it that is related to the slow sdsListOfPlayers SQLDataSource. It looks like in every postback, it's rebuilding the datasource and getting all data back from SQL
Server (wich is a total waste cause the list of players don't change after the load of page, it doeant need to go back to check on SQL server).
If I'm not clear, I could post cleaned code ;-)
I really hope I can get help cause i'm a little bit blocked on how to fix that
Many thanks for the time you take on this ;-) I really appreciate !
I have included a screenshot of my ScoreSheet page. I also have included a minimalist chunk of code of one of the Line UP GridView with english terms in it.
The red arrow indicate spot where it's taking about 5-10 seconds to execute operation :
- Edit
- Delete
- Updates
- Saves in database with CodeBehind
The Green arrow indicate the dropdownlists that are using the evil SQLDataSource that seemd to make every events slow in page. Those dropdownlist are inside the Visiting and Home GridView that are used to build up the Line UP. In total, there is 6 dropdownlist
that use the Evil SQLDataSource in those 2 top gridview (Home and visiting line up)
The dropDownlist are used inside :
- <EditTemplate>
- <footerTemplate>
- <EmptyTemplate>
So the test I did to find out why it's soo slow : modify the Evil SQLDatasource that get all the available players in the league and change the query to a Select top (10) ........... When I do that, All event are done instantly .... no more 5-10 seconds
to wait between each event.
Also, all my gridviews have there own updatePanel.
thanks again for the help ;-)
Little chunk of code that i've done to show you a lineup gridview (really simple one) :
Hi, yes the green arrows are the dropdown list that share the same sqldatasource. With the 2 gridviews, theres 6 dropdownlist using the same datasource The red arrow are events that takes really long to execute
Hi, yes the green arrows are the dropdown list that share the same sqldatasource. With the 2 gridviews, theres 6 dropdownlist using the same datasource The red arrow are events that takes really long to execute
Hi,
Maybe you should shorten your project's design，and just use AJAX JQuery to fetch and bind to the Dropdownlist……
marric01
Member
17 Points
34 Posts
SQLDataSource
Jun 27, 2012 07:37 PM|LINK
Hi, I have a SQLDatasource (sdsPlayerList) with lot's of data in it (it's list of all players available in league) and it's used with a dropdownlist in a gridview : gvLineUpTeam.
When my page is open, I guess that the SQLDatasource is filled with the list of players once at load ?
I wanted to know if the sdsPlayerList was kind of re-binded or reloaded at each postback cause a simple edit or update command in the page (in another gridView) is really long. I did this test : If I change my query in the StoredProc of my sdsPlayerList to a "select top 30 ..." everything is super fast everywhere in my page).
How a edit/update/delete in another GridView (gvLineUpPenality) that has nothing to do with the sdsPlayerList make everything slow in page ?
Is there a way that the sdsPlayerList never get re-bind ou reload unless I do it in codebehind ?
All my gridview are inside different updatepanel (but not my SQLDataSource)
Thanks for any suggestion !
Richard
ryanbesko
Contributor
3561 Points
619 Posts
Re: SQLDataSource
Jun 27, 2012 07:49 PM|LINK
With your full list loaded in the dropdown, look at the source of the page in your browser. Each choice on that list is a line of html code. Therefore if you have a really long list, your page is bigger, hence slower. Anytime you have a huge dropdown list this will be a problem.
MattsDotNetU...
Contributor
3178 Points
515 Posts
Re: SQLDataSource
Jun 27, 2012 07:52 PM|LINK
You can by creating the DataSource in on page_load and wrapping it with an IsPostback check.
if (!IsPostBack) { SqlDataSource dataSource = new SqlDataSource(); GridView2.DataSource = dataSource; GridView2.DataBind(); }Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: SQLDataSource
Jun 29, 2012 02:31 AM|LINK
Yes, when you bind the DataSource to the Dropdownlist, it will automatically fetch all the records from the real table accroding to your real SQL statement into the Dropdownlist as its elements.
I don't suggest you using DataSource if your sql statement is always chaning dynamically.... I think you can do with a SqlDataAdapter.Fill into a DataTable, and then set the DataTextField and DataValueField and in the end call "DataBind()" method to the Dropdownlist.
yes, just do data-binding directly in the if(!IsPostBack){.....}
marric01
Member
17 Points
34 Posts
Re: SQLDataSource
Jun 30, 2012 03:55 AM|LINK
Hi,
My main goal if to find a way to improve performance of my page (It can be a little slow on first open).
Maybe I'm not explaining correcly what I'm trying to do and I'm pretty new to this programming and english is not my main language ;-).
So in my page, I have two gridview : One for visiting team and one for receving team. So those gridview are for building the line up of the game. (All my gridviews have there own seperate updatePanel)
I also have 4 other gridview for the production and penalties of players for each team (you can only add goal, assists and penalties if player were previously added in the line up, so the dropdownlist to select a player is pretty small)
Now, in my page I have created some sqldatasource to fill gridviews and dropdownlist inside gridviews. The sqldatasource wich cause (I think) the page to be very slow when performing operation that trigger postback is the sqldatasource that contains all the players in the league (sdsListOfPlayers ). That sqldatasource is used inside the 2 line up gridview to fill the dropdownlist in <footerTemplate>, <editTemplate> and <emptyTemplate>.
So if i click edit command in a row in the lineup gridview, I have a dropdownlist and I can modify the players name. In the footerTemplate and emptyTemplate, I can add a player in the line up from a dropdownlist with all the players name and there id's.
But the edit, insert and delete is very slow in all other gridviews even if theres nothing in it that is related to the slow sdsListOfPlayers SQLDataSource. It looks like in every postback, it's rebuilding the datasource and getting all data back from SQL Server (wich is a total waste cause the list of players don't change after the load of page, it doeant need to go back to check on SQL server).
If I'm not clear, I could post cleaned code ;-)
I really hope I can get help cause i'm a little bit blocked on how to fix that
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: SQLDataSource
Jun 30, 2012 05:24 AM|LINK
Hi again:)
If possible，show us your running screenshots and quote which places are slow？And tell us step by step which you have done……
Many thanks！
marric01
Member
17 Points
34 Posts
Re: SQLDataSource
Jun 30, 2012 03:11 PM|LINK
Hi again !
Many thanks for the time you take on this ;-) I really appreciate !
I have included a screenshot of my ScoreSheet page. I also have included a minimalist chunk of code of one of the Line UP GridView with english terms in it.
The red arrow indicate spot where it's taking about 5-10 seconds to execute operation :
- Edit
- Delete
- Updates
- Saves in database with CodeBehind
The Green arrow indicate the dropdownlists that are using the evil SQLDataSource that seemd to make every events slow in page. Those dropdownlist are inside the Visiting and Home GridView that are used to build up the Line UP. In total, there is 6 dropdownlist that use the Evil SQLDataSource in those 2 top gridview (Home and visiting line up)
The dropDownlist are used inside :
- <EditTemplate>
- <footerTemplate>
- <EmptyTemplate>
So the test I did to find out why it's soo slow : modify the Evil SQLDatasource that get all the available players in the league and change the query to a Select top (10) ........... When I do that, All event are done instantly .... no more 5-10 seconds to wait between each event.
Also, all my gridviews have there own updatePanel.
thanks again for the help ;-)
Little chunk of code that i've done to show you a lineup gridview (really simple one) :
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Gestion_Default2" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"> </ajaxToolkit:ToolkitScriptManager> <asp:SqlDataSource ID="sdsLineUpOfPlayers" runat="server" ConnectionString="<%$ ConnectionStrings:DB_36624_hlm_ConnectionString %>" DeleteCommand="DELETE FROM [tblLineUp] WHERE [ID_PK] = @ID_PK" InsertCommand="INSERT INTO [tblLineUp] ([ID_PK], [FK_PLAYER]) VALUES (@ID_PK, @FK_PLAYER)" SelectCommand="SELECT tblLineUp.ID_PK, tblLineUp.FK_PLAYER, tblPersonne.strNomPersonne + N', ' + tblPersonne.strPrenomPersonne AS strFullName FROM tblLineUp INNER JOIN tblPersonne ON tblLineUp.FK_PLAYER = tblPersonne.intIDPersonne" UpdateCommand="UPDATE [tblLineUp] SET [FK_PLAYER] = @FK_PLAYER WHERE [ID_PK] = @ID_PK"> <DeleteParameters> <asp:Parameter Name="ID_PK" Type="Int32" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="ID_PK" Type="Int32" /> <asp:Parameter Name="FK_PLAYER" Type="Int32" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="FK_PLAYER" Type="Int32" /> <asp:Parameter Name="ID_PK" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="sdsListOfPlayers" runat="server" ConnectionString="<%$ ConnectionStrings:DB_36624_hlm_ConnectionString %>" SelectCommand="SELECT intIDPersonne, strNomPersonne + ', ' + strPrenomPersonne as strFullName FROM tblPersonne"> </asp:SqlDataSource> <br /> <br /> <asp:GridView ID="gvLineUpVisitingTeam" runat="server" AutoGenerateColumns="False" DataSourceID="sdsLineUpOfPlayers" ShowFooter="True" DataKeyNames="ID_PK" AllowSorting="True" Width="95%" ShowHeaderWhenEmpty="True"> <EmptyDataTemplate> <tr class="AltRowStyle"> <td colspan="2"> <asp:ImageButton ID="Insert" runat="server" CausesValidation="True" CommandName="NoDataInsert" ImageUrl="~/images/GridViewCommand/Button-Add-icon.png" ToolTip="Ajouter" /> <ajaxToolkit:ComboBox ID="ddlPlayer" runat="server"> </ajaxToolkit:ComboBox> </td> </tr> </EmptyDataTemplate> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:TemplateField HeaderText="Name of Player" SortExpression="strFullName"> <EditItemTemplate> <asp:DropDownList ID="ddlPlayer" runat="server" DataSourceID="sdsListOfPlayers" DataTextField="strFullName" DataValueField="intIDPersonne" SelectedValue='<%# Bind("FK_PLAYER") %>'> </asp:DropDownList> </EditItemTemplate> <ItemTemplate> <asp:Label ID="strFullName" runat="server" Text='<%# Eval("strFullName") %>'></asp:Label> </ItemTemplate> <FooterTemplate> <asp:DropDownList ID="ddlPlayer" runat="server" DataSourceID="sdsListOfPlayers" DataTextField="strFullName" DataValueField="intIDPersonne" SelectedValue='<%# Bind("FK_PLAYER") %>'> </asp:DropDownList> <asp:ImageButton ID="Insert" runat="server" CausesValidation="True" CommandName="Insert" ImageUrl="~/images/GridViewCommand/Button-Add-icon.png" ToolTip="Ajouter" /> </FooterTemplate> </asp:TemplateField> </Columns> </asp:GridView> </div> </form> </body> </html>And this id the screenshot of the ScoreSheet:
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: SQLDataSource
Jul 01, 2012 01:54 AM|LINK
so you mean that the green pointers will make the page very slow？And have you shared the same datasources for each of the Dropdownlist？
marric01
Member
17 Points
34 Posts
Re: SQLDataSource
Jul 01, 2012 02:58 AM|LINK
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: SQLDataSource
Jul 01, 2012 04:04 AM|LINK
Hi,
Maybe you should shorten your project's design，and just use AJAX JQuery to fetch and bind to the Dropdownlist……