Search code examples
c#sqlwpftimeoutexception

Transition from COM context error in WPF when fetching large amount of data from SQL

I am working on wpf application. This application is used to show the reports. For this I have two tabs: one tab to select the columns from listview and other filters, the second tab shows the result from sql table. Here for dynamic query I am making a query on server side and pass this in stored procedure as parameter there I am executing it using execute method.

Problem :

It works fine if I select some columns out of total 170, but when I select all columns in list view and try to go on other tab it remains on the same tab and throws the exception:

The CLR has been unable to transition from COM context 0xc40048 to COM context
0xc401b8 for 60 seconds.

there is thousands of rows fetch for this.

Question :

1.)how i can make execution fast for query.

2.)Steps to make table more fast for fatching data.

Solution

  • A few ideas:

    • Use BackgroundWorker class and work with the Dispatcher in WPF.
    • Page the results - you could also create the infinite scroll effect
    • Use DataReader instead of DataSet
    • Hide/disable rendering of the control on Tab 2, bind the data, re-enable control. In WinForms, you'd use SuspendLayout / ResumeLayout
    • Change the UI - I can't imagine how a human can process 170 columns ... perhaps there is a even better (often simpler) presentation of the data you can provide

    Ultimately, you need to determine where in your call stack the performance problem is. The Visual Studio debugger should help with this, but you may need to use some other tools. Use SQL Profiler to analyze your Stored Procedure/SQL Server behavior. For the network connection, try something like WireShark.

    EDIT: added Dispatch/BackgroundWorker link --- you still need to know where the timeout is occurring.