Optimizing performance of a .NET application with Data bindings to Excel VSTO ListObjects - 06/05/2025 02:21 EDT
Budget: $25 – $50 USD
Current architecture of the application
- In-memory database runs a query
- Results of the query are extracted using a DBReader into a System.Data.DataTable object (DataTable is used as an intermediary object for binding, but its features are not really needed as data is read-only).
- This object is used as the source of the binding for a ListObject in the Excel VSTO Add-In
- When the query is re-run, the ListObject is updated with the new data.
Issue to solve: performance is slow, with 2 approaches having been tested:
(1) Current approach:
- A new DataTable is created every time a query is run
- The ListObject binding is re-established every time, which updates the data.
- However this introduces a 1500ms delay for each run.
(2) Alternative approach:
- Keep a DataTable bound to the ListObject
- On query, first clear the DataTable (this is only to remove data from the ListObjects, since they have a peculiar updating behavior when placed next to each other, which requires to clear them before updating them with new data).
- Then re-load the data into the DataTable using a DBReader.
- This is ever slower, e.g. from 80ms the first load to 8000ms after a couple of runs for only 17k rows
- Possibly due to the ListObject trying to merge the new data with the old one, despite the DataTable having been cleared.
Task
- Propose code changes to optimize performance for the sequence from loading data from the DB to updating the ListObject (logging is in place and measures the appropriate times).
- Objective would be to go down to around 100ms for data refresh.
Requirements
- Strong expertise in .NET System.Data library and data bindings
- Strong expertise in Excel VSTO in particular ListObjects
- Ability to test using Visual Studio and Microsoft Excel 365.
- Please only bid if you have past experience in these topics.
- First block of 8 hours, then reevaluate based on results.
- In-memory database runs a query
- Results of the query are extracted using a DBReader into a System.Data.DataTable object (DataTable is used as an intermediary object for binding, but its features are not really needed as data is read-only).
- This object is used as the source of the binding for a ListObject in the Excel VSTO Add-In
- When the query is re-run, the ListObject is updated with the new data.
Issue to solve: performance is slow, with 2 approaches having been tested:
(1) Current approach:
- A new DataTable is created every time a query is run
- The ListObject binding is re-established every time, which updates the data.
- However this introduces a 1500ms delay for each run.
(2) Alternative approach:
- Keep a DataTable bound to the ListObject
- On query, first clear the DataTable (this is only to remove data from the ListObjects, since they have a peculiar updating behavior when placed next to each other, which requires to clear them before updating them with new data).
- Then re-load the data into the DataTable using a DBReader.
- This is ever slower, e.g. from 80ms the first load to 8000ms after a couple of runs for only 17k rows
- Possibly due to the ListObject trying to merge the new data with the old one, despite the DataTable having been cleared.
Task
- Propose code changes to optimize performance for the sequence from loading data from the DB to updating the ListObject (logging is in place and measures the appropriate times).
- Objective would be to go down to around 100ms for data refresh.
Requirements
- Strong expertise in .NET System.Data library and data bindings
- Strong expertise in Excel VSTO in particular ListObjects
- Ability to test using Visual Studio and Microsoft Excel 365.
- Please only bid if you have past experience in these topics.
- First block of 8 hours, then reevaluate based on results.