Efficient retrieval of records with dynamic columns from db into Grid

Hi Guys,
This is a general .Net question that relates to fetching data from database and filling up a grid on the user interface,

Ok the problem is that I am doing some research as to how i can improve the performance of existing code, while we search and fetch records from db,

We've a desktop application written in .Net having SQL server 2000 database at the backend, There are different stored procedures (SP) for a given operation eg search , save, etc...

While searching a given type of records lets say workorders , we execute SP while passing it given filters etc and get the results in a datareader, then we create create workorderinfo objects and fill them with their data from datareader, finally make a list (IList) of those objects and attach the list to the grid (ultragrid of infragistics) by setting its datasource to be that list,.

The workorderInfo objects which the SP returns has like 50 columns, always same columns (mix of strings , dates, intergers etc) and when we've too many records like 20,000 or so the fetch process and especially filling the grid with list becomes very slow, and I've to improve it, ....

Now the plan is to let the users choose the fields/columns they want, so that I can give them some control on speed and amount of data that is brought after search from db into the grid,...

now I've to modify SP which will only return required columns dynamically, so no of columns will be variable every time...

Now the problem I see is that the workorderinfo objects I had earlier now i can only fill their filelds which have been requested by user and leave other fields/members to be empty,.... now this thing seems like a bit odd to me , since there is no point of having empty fields in object which would still consume some memory which is not even required,...

eventually I have to fill up the grid faster and this might not be that useful i think, Could you people suggest me what approach should best fit here,.. that is more performace oriented and efficient... seems llike i need to getrid of object creation for each row and do something else..

:blush:
this seems like a debatable thing, but please put your suggestions as to what you guys think what approach should be considered,...

Regards,
Ahmed

i usually implement this one in my web applications that require large table display - pagination. you may take a look at this link http://msdn2.microsoft.com/en-us/library/ms171624.aspx which incorporates almost the same principle. hope that helps :wave:

Have the SP return a cursor that has only the columns requested. Use a datadapter and datatable to download the data. use a datagrid or datagridview with autogeneratcolumns=true to show the data

Thank you cjard and Thread1 for your replies,...
you suggested me to use, datatable and dataadapter would it solve my dynamic column problem only or would it address the performance issue too, Is it better than datareader and IList in terms of speed as well ??

cjard, did you recommend me to use Cursors IN SP irrespective of whether I use paging or not, ? or cursors are to be used only when pagnation is done..

If for some reasons I am not let to do paging, since its a desktop app, then do you think Using datatable and dataadapter would then be an option again..??

Cursor is a database feature, you can use it with the pagination for further enhancement. as you can see in the link, the implementation of IDataPageRetriever (SupplyPageOfData) uses only a regular SQL statement (order-by).

I'd look very closely to putting a cap on the max records that they can pull. If they really need 20000 records, then maybe it should be a report! As far as performance goes you'd be hard pressed to do better than DataReader. To filter out downloading unwanted columns, you'd have to do dynamic SQL, which could lead to security risks and worse performance due to compile on demand for the SP on the database.

So, I'd push for a records cap and if that's not possible, I'd look into background threading the data retrieval process, so at least your app is responsive. . .

You may want to look at this

Microsoft.Samples.VirtualListView.VirtualListView()