We help IT Professionals succeed at work.

Gridview displays data abnormally slowly.  Any way to speed it up?

bmagers
bmagers asked
on
902 Views
Last Modified: 2010-04-21
I have a page with a calendar and a gridview.  The user selects a date, week, or month on the calendar, which calls a stored procedure and returns the data for display in the gridview.

The problem is, the gridview displays the data very slowly.  If there is a lot of data (a typical month will have about 100 lines listed) it's so slow that the page times out.  I know the stored procedure isn't to blame, because running it directly from the query analyzer yields the results in less than a second.

Any ideas?

<%@ Page Language="C#" %>
 
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        Calendar1.SelectedDates.Add(DateTime.Now);
    }
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Calendar and GridView</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Calendar ID="Calendar1" runat="server" SelectionMode="DayWeekMonth" />
        <asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
            DataSourceID="SqlDataSource1" EnableViewState="False">
            <Columns>
                <asp:BoundField DataField="pickupdate" HeaderText="Date" SortExpression="pickupdate" />
                <asp:BoundField DataField="daycode" HeaderText="Route" SortExpression="daycode" />
                <asp:BoundField DataField="location" HeaderText="Location" SortExpression="location" />
                <asp:BoundField DataField="region" HeaderText="Region" SortExpression="region" />
                <asp:BoundField DataField="records" HeaderText="Records" SortExpression="records" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:StandardConnectionString %>"
            SelectCommand="_c_MailRoutes_List" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="Calendar1" Name="DateFirst" PropertyName="SelectedDate"
                    Type="DateTime" />
                <asp:ControlParameter ControlID="Calendar1" Name="DateCount" PropertyName="SelectedDates.Count"
                    Type="Int16" />
            </SelectParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

Open in new window

Comment
Watch Question

There doesn't appear to be anything wrong with the gridview itself.  How complex is the stored proceedure?

Author

Commented:
The stored procedure is somewhat complex, and I can post it if you like.  But like I said, when I run it in query analyzer it runs in less than a second, even if I give it a date range of 30 days and it returns over a hundred records.

Commented:
Running the query from the QA isn't necessarily a reliable indicator of how it will perform in your app. Have you run a profiler against it to confirm it is executing in good time?
What kind of timeout message are you receiving?

Author

Commented:
I checked the execution time in the Profiler, launching the stored procedure from the ASP.NET app and from the query analyzer.  Time required from the query analyzer: 1 second.  Time required from the ASP.NET app: about 30 seconds.  That's got to be the source of the timeout.  The error I get is "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

So clearly the bottleneck is how slowly ASP.NET executes the stored procedure.  Is there any way to get the data into ASP.NET faster?

Commented:
Can you post the stored procedure? The problem isn't necessarily ASP.NETs fault, the way SQL Server generates execution plans can lead to odd results like this sometimes.

Author

Commented:
The stored procedure is fairly complex.  I'm attaching it below.  There are some extra fields in here which are required for the page, but which I omitted from the code snippet above for readability.



[dbo].[_c_MailRoutes_List]
@cg smallint, @DateFirst smalldatetime, @DateCount smallint
 
output as
 
select distinct
	trips.tripno,
	trips.cc,
	trips.pickupdate,
	trips.daycode,
	daycode.pdf,
	case zip.state
		when callgroups.homestate then zip.city
		else zip.city + ', ' + zip.state
	end as location,
	abbreviation as region,
	trips.records,
	clients.nickname,
	case count(distinct calls.callno)
		when '0' then null
		else count(distinct calls.callno)
	end as calls,
	case sum(distinct hours)
		when null then '0'
		else sum(distinct hours)
	end as hours,
	case count(distinct drivers.namelast)
		when 0 then null
		when 1 then min(distinct drivers.namefirst + ' ' + drivers.namelast)
		else min(distinct drivers.namefirst + ' ' + drivers.namelast + '...')
	end as driver
from
	trips
	join zip on zip.zip = trips.zip
	join clients on clients.cc = trips.cc
	join daycode on daycode.daycode = trips.daycode
	join callgroups on clients.callgroup = callgroups.callgroup
	left join calls on calls.tripno = trips.tripno
	left join tripstatistics on (tripstatistics.tripno = trips.tripno and tripstatistics.triptype = 'M')
	left join drivers on tripstatistics.driverid = drivers.driverid
where pickupdate >= @DateFirst
	and pickupdate <= @DateFirst + @DateCount - 1
	and ((status <> 'I'  and status <> 'O') or (status = 'I' and trips.pickupdate >= effective) or (status = 'O' and trips.pickupdate <= through))
	and clients.callgroup = @cg
group by
	trips.tripno,
	trips.cc,
	trips.daycode,
	trips.pickupdate,
	zip.city,
	zip.state,
	region,
	clients.nickname,
	trips.records,
	pdf,
	callgroups.homestate
order by
	trips.pickupdate,
	trips.daycode

Open in new window

Commented:
I can't see anything that should cause any problems but I'm confused about a couple of things:
1. Where does the value of @cg come from?
2. Is @DateCount an output variable or is that just the way it's been pasted? If it is then why?

Have you tried programatically calling the stored proc? ie instead of using an sqldatasource manually call the stored procedure in the code-behind to see if that makes any difference?

Are there and transactions or other database operations on the page that could be locking the tables?

Author

Commented:
1. @cg comes from a session variable, a number representing the logged in user.  (I removed that from the code for brevity.)

2. This is a mistake, it shouldn't be an output variable.  I changed that, but it doesn't seem to make a difference performance-wise.

I don't think there's an issue with locking.  I haven't seen any conflicts listed in the error log.  Plus, it always runs slow in ASP.NET and always runs fast from the query analyzer, and that seems too predictable to be a locking problem.

I will try calling the stored procedure from the code and see if that helps.

Author

Commented:
Calling the stored procedure and filling the gridview from code doesn't seem to help.  It runs just as slow.

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
All good points.  I have rewritten the stored procedure to incorporate them.

Unfortunately, it still runs slow in ASP.NET.

Commented:
What version of sql server are you running? Is it fully service packed up?

The reason I mentioned transactions before is that we had a situation before where DTS had crashed and was causing a timeout on just one stored procedure, even though we could run it normally from Query Analyser etc. A server restart fixed it.

I assume your connection strings etc are all standard? I'm kinda stuck on what else to suggest. Maybe try adding (with nolock) on your table joins just to see if it makes any difference.


Author

Commented:
I'm running SQL Server 2005, fully service packed.

I've restarted many times, but it doesn't seem to do any good.

Connection strings are all standard.  In fact, I normally use the same one every time.

I tried with (nolock) and no luck there either.

I also followed a couple of leads I found from web searches:

1) Some have had slowness due to the database optimizing one fork in the code over another when creating the execution plan at compile time.  I tried forcing it to recompile every time.

2) Setting ARITHABORT to ON.  Apparently has something to do with how the optimizer uses indexing.

Neither one of these improved matters either.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Zowee!  Replace the parameters with hardcoded values and the performance issue disappears.

This led me to believe that the problem really is as described in web search solution #1 above.  The compiler created the execution plan using the values passed through the parameters at first use, and so it was less than optimized for other values.

I had previously tried to solve it by forcing the stored procedure to recompile on every use.  That didn't work.  Maybe any performance gain was offset by the compile time.  Or maybe I just implemented it wrong.

Anyhow, I tried another solution that I encountered in that same web search, which is to declare local variables in the stored procedure and set them equal to the parameters, and then use the local variables from there on.  That did it!

Thank you for all your help!

Author

Commented:
This is my first time splitting points, so hopefully I've got this right.

NazoUK: I'm awarding you most of the points because you led me (in conjunction with the off-EE information I learned) to the solution.

GreymanMSC: I'm giving you some points as well for helping me to improve my code.

Thanks very much to both of you!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.