Filling in datagrid columns from database after page is already loaded

views:

262

answers:

Filling in datagrid columns from database after page is already loaded

I have a web page that loads some data via a SQL query into a datagrid. I was asked to add a new column. During testing I discovered that pulling this extra column of data from the database slows the query down substantially. What used to be a sub-second query now takes about 3-4 seconds. I've checked the database and all the appropriate indices are in place, and a database change wouldn't be practical here.

Anyway the slow down in the page loading could really impact productivity.

If it's possible and practical, this is what I would like to do. I'd like to render the datagrid as before with the original query, but leave and extra column blank. Then after the page is rendered use Ajax to go back to the server and get the data for the remaining column and populate it. That way, those users who don't need the extra column to do their work, don't have to wait so long for the page to render, but those who need it can just wait a few seconds and the data will be there.

The problem is, I don't have the faintest idea how to put this together. Any suggestions?

How much of a slow down?
How many rows are returned?

If you are going to AJAXify the grid for one column you may as well do the whole grid, at which point you may wish to invest in third party controls from Telerik for example, or possibly from the YUI framework library.

But in truth I believe your SQL query is the root cause for your performance problem at the moment.

Assuming the query is returning a significant number of row, have you considered paging the data on the database side?

BlackMael 2008-11-22 21:51:44

The queries used to take under 1 second to run. Now they take 3-4 seconds.We're only returning around 20-40 rows so paging wouldn't help much.

Aheho 2008-11-23 02:40:33

I know that the sql is the root cause of the problem. However little can be done at the moment. Bringing in this other column requires 3 more tables to be added to the join, and it's an outer join to boot. All the proper indicies are in place, and denormilization is not an option.

Aheho 2008-11-23 02:42:23

Then surely the focus should be on the query.What does the execution plan show? I assume the query is working over a very large data set. Can the query not be reworked to filter out as much of the data as possible? There must be some inefficiencies in there somewhere?

BlackMael 2008-11-23 02:56:56

If the problem is the database, then messing with anything else is time wasted. It's not credible to say that 3 more tables in a join have this impact if they are properly indexed and the query is properly written. And whether it's an outer join is irrelevant.

le dorfier 2008-11-23 03:24:35

I disagree. Without making changes to the schema, there is nothing I can do with indices or restructuring the query to improve performance. I know what I'm doing in this regard. I COULD change which index is clustered on a table to improve performance, but then other queries would suffer.

Aheho 2008-11-23 13:43:03

Give the user an option to display the extra column or not.

Then have two queries that can bind to the GridView, one that returns the extra column and one that doesn't.

Then it is up to the user to decide which "view" they want.

So that they do not have to decide every time they open or refresh the page, you persist their selection.

BlackMael 2008-11-23 03:08:50

I considered this option. If the AJAX strategy proves too complex, I'll probably go this route.

Aheho 2008-11-23 13:43:59

ansaurus

tags:

views:

answers:

Filling in datagrid columns from database after page is already loaded

related questions