CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Location
    London
    Posts
    198

    Red face 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..


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

    Regards,
    Ahmed
    He is not strong and powerful, who throws people down, but he is strong who withholds himself from anger
    MyWeb

  2. #2
    Join Date
    Jan 2003
    Location
    7,107 Islands
    Posts
    2,487

    Re: Efficient retrieval of records with dynamic columns from db into Grid

    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
    Busy

  3. #3
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Efficient retrieval of records with dynamic columns from db into Grid

    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
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  4. #4
    Join Date
    Mar 2003
    Location
    London
    Posts
    198

    Re: Efficient retrieval of records with dynamic columns from db into Grid

    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..??
    He is not strong and powerful, who throws people down, but he is strong who withholds himself from anger
    MyWeb

  5. #5
    Join Date
    Jan 2003
    Location
    7,107 Islands
    Posts
    2,487

    Re: Efficient retrieval of records with dynamic columns from db into Grid

    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).
    Busy

  6. #6
    Join Date
    Aug 2007
    Location
    Minneapolis
    Posts
    155

    Re: Efficient retrieval of records with dynamic columns from db into Grid

    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. . .

  7. #7
    Join Date
    May 2002
    Posts
    511

    Re: Efficient retrieval of records with dynamic columns from db into Grid

    You may want to look at this

    Microsoft.Samples.VirtualListView.VirtualListView()

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured