none
How to link a database table and an in-memory table RRS feed

  • Question

  • Does anyone know if it’s possible to link a table in a SQL database to an in-memory table? Here’s my situation…

    I’m working on a legacy app that has a screen with 11 grids on it. The grids are populated via 11 different disconnected datasets. Once the user has selected multiple rows on each grid, a query is performed returning the rows from a datatable where the criteria match the selections from the 11 grids.

    The legacy app writes the selections from the 11 grids to 11 different tables in the database and then runs a stored procedure that evaluates the data to return the proper rows from the “master” table. This results in a lot of database activity, and in a multi-user environment we’re suffering serious performance issues.

    My thought is to reference the disconnected datasets in memory instead of writing them back to the database. Seems like this would greatly improve performance. So here’s an example of the query I’m looking to run. (dbTable is a table in the database and memTable resides in my disconnected dataset.)

    select field1, field2from dbTable
    
    where dbTable.field1 in
    
    (select field1 from memTable where
    
    memTable.field3 = 1)
    

    Can anyone think of a way to implement this?

    Thanks in advance,

    Steve. 

    Wednesday, May 4, 2011 3:55 PM

Answers

  • There is really no such concept in ADO.NET. Other than a DataReader, which is a forward/read-only connection to data, DataSets/DataTables operate disconnected as I'm sure you well know.

    In addition, loading 50,000 items into a DataTable or DataGridView isn't really feasible. At a minimum you should use data paging and only read in several hundred at a time. Otherwise, you are correct, performance will not be acceptable. When you're working with processing data of this magnitude, it's batch job that's likely to take some time to execute.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by steveosmith Tuesday, May 10, 2011 3:09 PM
    Monday, May 9, 2011 11:24 PM

All replies

  • I'm not sure why you would need to write the selected criteria into the database. The IN clause can be a comma separated list that you build "on the fly" from the criteria selected in the grids. Below is an example that does not require a sub query:

    SELECT companyname, country 
    FROM customers
    WHERE country IN ('Austria', 'Belgium', 'Germany', 'Italy')
    
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by steveosmith Wednesday, May 4, 2011 6:36 PM
    • Unmarked as answer by steveosmith Monday, May 9, 2011 8:16 PM
    Wednesday, May 4, 2011 6:24 PM
  • YES!! Good point. I can simply loop thru the 11 datatables, building my query on the fly. Makes sense. I'd gotten too hung up on the existing schema. Thanks for the great feedback!!

    S.

    Wednesday, May 4, 2011 6:36 PM
  • Well, that would have been a good suggestion, if only there weren't so many possible selections for the IN clause. The grids can each have up to 50,000 selections in them. When everything is selected in each grid, performance is unacceptable. 

    I can't help but think it would run faster if it used linked tables. But I don't want to write the selections to the database. Thus, my original question about linking my one database table to my eleven in-memory tables.

    So, think it's possible?

     

    Thanks again,

    Steve.

    Monday, May 9, 2011 8:21 PM
  • There is really no such concept in ADO.NET. Other than a DataReader, which is a forward/read-only connection to data, DataSets/DataTables operate disconnected as I'm sure you well know.

    In addition, loading 50,000 items into a DataTable or DataGridView isn't really feasible. At a minimum you should use data paging and only read in several hundred at a time. Otherwise, you are correct, performance will not be acceptable. When you're working with processing data of this magnitude, it's batch job that's likely to take some time to execute.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by steveosmith Tuesday, May 10, 2011 3:09 PM
    Monday, May 9, 2011 11:24 PM
  • Thanks, Paul, for confirming what I feared. I read that LINQ can connect to a virtual table, and sure enough, in my preliminary test it works. Now I'll see if it can scale to my app's needs. Thanks again for your feedback.

     

    Steve.

    Tuesday, May 10, 2011 3:09 PM