How do I speed up ajax update panel

I have an update panel with a gridview. I have an insert function, which inserts data into a sql2005 database, then displays it in the gridview. The problem is that the delay is slow from when it round trips the server on insert. My server isn't slow, but is there anyway to make it go quicker or am I out of luck?
LVL 1
mlg101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ted BouskillSenior Software DeveloperCommented:
AJAX actually adds a lot of overhead to round trips (which is required in an update).  AJAX is what used to be called XML data islands.  It was really intended as a way to avoid round trips.  The idea was to send the user a snapshot of data in a format that they could review the data and sort or drill into it without a trip to the server.

The conversion from SQL to XML to your browser then to HTML is time consuming.  You might be able to tune the queries to improve performance but XML adds overhead that won't be easy to optimize.
0
RejojohnyCommented:
i think the problem you have is with your network .. i assume when you say that your server isn't slow, you also mean that your SP or queries to update the database is happening fast .. if yes, check the network connection .. check the client machine where you are testing ..is there a lot of software running? do you have the update panel just around the grid? remove the update panel and check whether it takes the same time .. if yes, then the problem is really with your network connection ..

Rejo

0
mlg101Author Commented:
sorry its taken so long to get back on this one. Right now I'm using objectdatasource with data adapter. Do you think if i used a sql datasource it would be quicker?

The update panel is only around the grid and the sql query is pretty simple, so I'm not sure what else I can do.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

RejojohnyCommented:
the sql datasource must be faster .. but i do not think the diference would be that big .. anyway, did you fiond the bottleneck? is it the network?
0
Ted BouskillSenior Software DeveloperCommented:
The SQL version of all database objects in ADO.NET are faster.
0
mlg101Author Commented:
maybe i will try ado.net. its going to take awhile to figure all this out, because I'm a beginner.
0
mlg101Author Commented:
i did not find a bottleneck, i have no idea where to start looking
0
Ted BouskillSenior Software DeveloperCommented:
First off, try the insert using SQL Management studio on the SQL server box.  Also try the select to populate the grid.  If they are slow, then it's not a .NET code or network problem.  It means the query and database design need improvement.
0
RejojohnyCommented:
can you debug? i mean do you have the code to debug .. if yes, add a breakpoint in the code where it is fetching the data from the database .. see how much time it takes for that process .. if you notice that this is where the maximum time is, then run your query firectly in the database and see if the query is the problem .. if the query returns a result really fast, then it is the network between the webserver and database is the problem .. if all this works fast, then it the network between the web server the machine you are testing where the problem is ..

Rejo
0
mlg101Author Commented:
I tried using management studio express and the insert and select queries worked very fast. The insert I'm using is very simple:
INSERT INTO Attempt (OrdersID, Attempt, AttemptDate, AttemptTime, LinkID) VALUES (@OrdersID, @Attempt, @AttemptDate, @AttemptTime, @LinkID)

Tedbilly said that if it works fast in mangement stuido express, then it means the query and database design need improvement.

So how do I improve the query and database design?
0
RejojohnyCommented:
I am sure what Tedbilly meant is that if the query is slow in management studio then you need to rework on the query and table .. if they work fast, there is no need for any change in the design or the query .. make sure the query you used for test is the same query you are firing from the frontend ..

so i think we are back to front-end code or network issues ..

Rejo
0
mlg101Author Commented:
ya im using the same query in the test as on the application.

What do you mean by front-end code?

btw, Im using the same network connection to the database using management studio as in my application, so it wouldn't be the network would it?
0
RejojohnyCommented:
front-end means the code the C# or vb.net code that you might have written to connect to the database server and fire the query, get the result and bind to your controls ..

can you post your code please?
0
mlg101Author Commented:
Here is my code:
<asp:GridView ID="GridView2" width="800px" runat="server" AutoGenerateColumns="False" BackColor="White"
                        BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataKeyNames="AttemptID"
                        DataSourceID="AccessDataSource1" ShowFooter="True" >
                        <FooterStyle BackColor="White" ForeColor="#000066" />
                        <Columns>
                            <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                            <asp:TemplateField HeaderText="AttemptID" InsertVisible="False" SortExpression="AttemptID" Visible="False">
                                <EditItemTemplate>
                                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("AttemptID") %>'></asp:Label>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("AttemptID") %>'></asp:Label>
                                </ItemTemplate>
                                <FooterTemplate>
                                    &nbsp;
                                </FooterTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Link" SortExpression="LinkID">
                                <EditItemTemplate>
                                    <asp:label ID="linktextbox3" runat="server" Font-Size="x-small" width="10px" Text='<%# Bind("LinkID") %>'></asp:label>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="linklabel3" runat="server" Font-Size="x-small" Text='<%# Bind("LinkID") %>'></asp:Label>
                                </ItemTemplate>
                                <FooterTemplate>
                                    <asp:TextBox ID="newLinkID3" width="10px" Font-Size="x-small" runat="server" visible="false"></asp:TextBox>
                                </FooterTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Order" SortExpression="OrdersID">
                                <EditItemTemplate>
                                    <asp:label ID="label" width="35px" Font-Size="x-small" runat="server" Text='<%# Bind("OrdersID") %>'></asp:label>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="NewOrder" runat="server" Font-Size="x-small" Text='<%# Bind("OrdersID") %>'></asp:Label>
                                </ItemTemplate>
                                <FooterTemplate>
                                    <asp:TextBox ID="newOrdersID" Font-Size="x-small" runat="server" width="35px" visible="false"></asp:TextBox>
                                </FooterTemplate>
                            </asp:TemplateField>                            
                            <asp:TemplateField HeaderText="Date" SortExpression="Date">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox3" runat="server" Width="75px" Height="12px" Font-Size="X-Small" Text='<%# Bind("AttemptDate") %>'></asp:TextBox>
                                    <asp:ImageButton runat="Server" ID="Imaget" ImageUrl="~/Calendar_scheduleHS.png" AlternateText="Click to show calendar" />
                                    <cc1:CalendarExtender ID="CalendarExtender7" runat="server" TargetControlID="TextBox3" PopupButtonID="Imaget">
                                    </cc1:CalendarExtender>
                                        <cc1:MaskedEditValidator ID="MaskedEditValidator50" runat="server" ControlToValidate="TextBox3"
                                        Display="Dynamic" ControlExtender="MaskedEditExtender50"></cc1:MaskedEditValidator>
                                        <cc1:MaskedEditExtender ID="MaskedEditExtender50" runat="server" Mask="99/99/9999" MaskType="Date"
                                        TargetControlID="TextBox3">
                                        </cc1:MaskedEditExtender>
                                </EditItemTemplate>
                                <FooterTemplate>
                                    <asp:TextBox ID="newDate" runat="server" Width="75px" Font-Size="X-Small" Height="12px"></asp:TextBox>
                                    <asp:ImageButton runat="Server" ID="Image2" ImageUrl="~/Calendar_scheduleHS.png" AlternateText="Click to show calendar" />
                                    <cc1:CalendarExtender ID="CalendarExtender5" PopupButtonID="Image2" runat="server" TargetControlID="newDate">
                                    </cc1:CalendarExtender>
                                            <cc1:MaskedEditValidator ID="MaskedEditValidator51" runat="server" ControlToValidate="newDate"
                                            Display="Dynamic" ControlExtender="MaskedEditExtender51"></cc1:MaskedEditValidator>
                                            <cc1:MaskedEditExtender ID="MaskedEditExtender51" runat="server" Mask="99/99/9999" MaskType="Date"
                                            TargetControlID="newDate">
                            </cc1:MaskedEditExtender>
                                </FooterTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label4" runat="server" Font-Size="X-Small" Width="75px" Height="12px" Text='<%# Bind("AttemptDate") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Time" SortExpression="Time">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox4" runat="server" width="50px" height="12px" Font-Size="X-Small" Text='<%# Bind("AttemptTime") %>'></asp:TextBox>
                                <cc1:MaskedEditExtender ID="MaskedEditExtender3" runat="server" Mask="99:99" MaskType="Time" AcceptAMPM="true"
                        TargetControlID="TextBox4">
                    </cc1:MaskedEditExtender>
                    <cc1:MaskedEditValidator ID="MaskedEditValidator3" runat="server" ControlExtender="MaskedEditExtender3"
                        ControlToValidate="TextBox4" Display="Dynamic"></cc1:MaskedEditValidator>
                                </EditItemTemplate>
                                <FooterTemplate>                                    
                                    <asp:TextBox ID="newTime" runat="server" width="50px" Font-Size="X-Small" Height="12px"></asp:TextBox>
                                <cc1:MaskedEditExtender ID="MaskedEditExtender4" runat="server" Mask="99:99" MaskType="Time" AcceptAMPM="true"
                        TargetControlID="newTime">
                    </cc1:MaskedEditExtender>
                    <cc1:MaskedEditValidator ID="MaskedEditValidator4" runat="server" ControlExtender="MaskedEditExtender4"
                        ControlToValidate="newTime" Display="Dynamic"></cc1:MaskedEditValidator>
                                </FooterTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label5" runat="server" width="50px" height="12px" Font-Size="X-Small" Text='<%# Bind("AttemptTime") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Attempt" SortExpression="Attempt">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox2" runat="server" Font-Size="X-Small" TextMode="MultiLine" Width="450px" height="34px" Text='<%# Bind("Attempt") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <FooterTemplate>
                                    <asp:textbox ID="newAttempt" runat="server" TextMode="MultiLine" Width="450px" Font-Size="X-Small" Height="34px"></asp:textbox>
                                    
                                    <asp:DropDownList ID="freq" runat="server" Font-Size="X-Small"
                                        Width="250px" DataSourceID="ods_attempt" 
                                        DataTextField="UsersEmail" 
                                        DataValueField="UserID"></asp:DropDownList><A HREF="JavaScript: addattempt()"><small>add</small></A><span style="font-size: 7pt"></span>
                                    <asp:LinkButton ID="Freq1" runat="server" Text="insert" Font-Size="X-Small" ></asp:LinkButton>
                                </FooterTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label3" Font-Size="X-Small" runat="server" TextMode="MultiLine" Width="450px" height="34px" Text='<%# Bind("Attempt") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField ShowHeader="False">
                                <EditItemTemplate>
                                    <asp:label ID="TextBox5" width="3px" runat="server"></asp:label>
                                </EditItemTemplate>
                                <FooterTemplate>
                                    <asp:Button ID="AddAttempt" runat="server" CommandName="Insert" Text="Add" />
                                </FooterTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="label8" runat="Server"></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                        </Columns>
                        <RowStyle ForeColor="#000066" />
                        <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
                        <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
                    </asp:GridView>
 
<asp:ObjectDataSource ID="ObjectDataSource2" runat="server" InsertMethod="InsertQuery"
                        OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="AttemptTableAdapters.AttemptTableAdapter" DeleteMethod="DeleteQuery" UpdateMethod="UpdateQuery">
                        <SelectParameters>
                        <asp:ControlParameter ControlID="OrdersID" Name="OrdersID" PropertyName="Text" Type="int32" />
                        </SelectParameters>
                        <InsertParameters>
                            <asp:Parameter Name="OrdersID" Type="Int32" />
                            <asp:Parameter Name="LinkID" Type="int32" />
                            <asp:Parameter Name="AttemptDate" Type="String" />
                            <asp:Parameter Name="AttemptTime" Type="String" />
                            <asp:Parameter Name="Attempt" Type="String" />
                        </InsertParameters>
                        <DeleteParameters>
                            <asp:Parameter Name="Original_AttemptID" Type="Int32" />
                        </DeleteParameters>
                        <UpdateParameters>
                            <asp:Parameter Name="OrdersID" Type="Int32" />
                            <asp:Parameter Name="LinkID" Type="int32" />
                            <asp:Parameter Name="AttemptDate" Type="String" />
                            <asp:Parameter Name="AttemptTime" Type="String" />
                            <asp:Parameter Name="Attempt" Type="String" />
                            <asp:Parameter Name="Original_AttemptID" Type="Int32" />
                        </UpdateParameters>
                        
                    </asp:ObjectDataSource> 
 
 
Code Behind:
 
Protected Sub GridView2_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) _
    Handles GridView2.RowCommand
    
        ' Insert data if the CommandName == "Insert" 
        ' and the validation controls indicate valid data...
        If e.CommandName = "Insert" AndAlso Page.IsValid Then
            ' Insert new record
            ObjectDataSource2.Insert()
        End If
        
    End Sub
    
    Protected Sub ObjectDataSource2_Inserting _
    (ByVal sender As Object, ByVal e As ObjectDataSourceMethodEventArgs) _
    Handles ObjectDataSource2.Inserting
        
        
        Dim NewDate As TextBox = _
            GridView2.FooterRow.FindControl("NewDate")
        Dim newTime As TextBox = _
            GridView2.FooterRow.FindControl("newTime")
        Dim newAttempt As TextBox = _
            GridView2.FooterRow.FindControl("newAttempt")
        Dim NewOrder As Label = lblID
        
        
        e.InputParameters("OrdersID") = lblID.Text
        e.InputParameters("LinkID") = LinkID.Text
        e.InputParameters("AttemptDate") = NewDate.Text
        e.InputParameters("AttemptTime") = newTime.Text
        e.InputParameters("Attempt") = newAttempt.Text
        
       
    End Sub

Open in new window

0
mlg101Author Commented:
The gridview datasourceID is supposed to be ObjectDataSource, and not AccessDataSource...no worries I fixed that.
0
RejojohnyCommented:
hmm .. there seems to be no issues with this code .. other than the network been the issue, i cannot think of any other problem due to which your code might be failing and there is no way we can test your network .. by the way, is it just the insert that is causing the problem or is the load (for the first time) itself taking a lot of time
0
mlg101Author Commented:
I took the insert functions out of the gridview footer and put them in a formview right below the gridview and it seemed to speed it up just a little bit. And yes, the pages does take a long time to load for the first time, but I figured it was because there are over 7000 lines of code on the page. But a lot of the lines are different functions that only fire, when doing a specific task. Did you have an idea because you asked if the page takes a long time to load?
0
RejojohnyCommented:
how many records are loaded the first time? the reason why I asked about the time taken for page load is to find if the issue is the insert statement that you tested or are there any other function that is slowing things down ... as mentioned above did you try debugging the application to check where the actual time is spent? is it when it fetches the record, the time between the page rendering the page seen in the browser? is the gridview paged? if there are more than 20 records to be shown, i would advice paging the gridview by setting allowpaging = true

Rejo
0
mlg101Author Commented:
usually ther are only one or two records displayed in the gridview. But my page loads by querying database to get one row from 2 different databases and binding to textboxes and dropdownlists, and there are a lot of columns in each row that are being queried. There are 3 gridviews on the page, but each one has 0 to 3 rows usually.

I'm trying to figure out how to debug, but I cant figure out how to get the information you said I should look for when debugging.
0
RejojohnyCommented:
to debug, you need to set debug= true in web.config and set a breakpoint on the page load and see which statement takes the maximum time .. when you say 2 different database, is from the same server?

Rejo
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TUS11Commented:
I have a situation where I fetch around 10 Records from database and displays them into a templated GridView control but it takes a lot of time on each click (as I fetch records at each time the next or previous button is clicked).
 
Also how can I achieve this from Ajax without postback. Any ideas for gzipping the DB output are also welcome.
 
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.