Have Geek Brain

GridView + ObjectDataSource issue - equally bad performance for large and small result sets

I was recently invited to work on an old .NET web application. Performance recently dropped rather poorly as the amount of data in the system quadrupled. It has been great for the previous two years.

He used the .xsd file to talk to a stored procedure that narrowed the results down enough (though not paging) and fed them to the GridView through the ObjectDataSource object.

The part I can't get down to takes about 7 seconds to load 1 result, 140 or 1200. It took a few seconds for 0 results. (GridView handles pagination)

Using the SQL profiler, we were able to see that the query took 6.9 seconds. Running the same query with the same parameters took 100ms in SQL Management Studio. I changed the options around to make sure it wasn't just a cached result. Long standby times are reliably reproducible.

I know the paging implementation will need to be done here, but at the moment I'm just looking in retrospect to find an explanation of why the page works equally badly for small and large sets ...

(a simple "GridView" screen is great for their needs - no need to manipulate HTML)

0


source to share


2 answers


We have the same problem:

  • Check if your statistics are up to date:
    SELECT 
        ObjectName = Object_Name (ind.object_id),
        IndexName = ind.name,
        StatisticsDate = STATS_DATE (ind.object_id, ind.index_id)
    FROM SYS.INDEXES ind
    order by STATS_DATE (ind.object_id, ind.index_id) desc


  1. Dropping SQL Server Cache:
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
  1. If none of these help, you can try to execute SSMS query plan with hints like "LOOP inner join" and "c (index = ...)".
+2


source


You can try to view the execution plan and the type of parameters that are sent from your application. For example, if an application passes nvarchar to compare against a varchar column, this can cause problems. http://weblogs.sqlteam.com/tarad/archive/2007/11/16/60408.aspx



+1


source






More articles:


All Articles