0

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.

Brett Veenstra
  • 47,674
  • 18
  • 70
  • 86
Sunny
  • 3,185
  • 8
  • 34
  • 66
  • 1
    At a minimum, posting the query would be helpful... – RB. May 09 '12 at 12:07
  • 1
    refer http://blog.wpfwonderland.com/2007/08/16/clr-has-been-unable-to-transition-from-com-context-for-60-seconds/ – Romil Kumar Jain May 09 '12 at 12:15
  • +1 for @RB. And you should look into paging your results. – Joshua Drake May 09 '12 at 12:15
  • @JoshuaDrake i am using paging on second tab. – Sunny May 09 '12 at 12:20
  • You need to page the results coming back from SQL, before they even get to the application. – Joshua Drake May 09 '12 at 12:26
  • @JoshuaDrake yes you are right how i can implement paging like this.Currently i am fetching all rows and then perform paging.i know this is bad idea.Please give some Idea to implement this – Sunny May 09 '12 at 12:31
  • @Sunny SQL Paging info provided below. – Joshua Drake May 09 '12 at 12:35
  • @Sunny: Could you provide some of the SQL you are using for the dynamic query? Are you using MS SQL Server, or something else? – Brett Veenstra May 09 '12 at 12:37
  • @BrettVeenstra i am using MSSQL2008 this is the dynamic query select RECORD_TIMESTAMP as DateRecorded,ROUND (T_E1 ,2 )as T_E1,ROUND (T_E2 ,2 )as T_E2,ROUND (T_E3 ,2 )as T_E3 from TBL_SENSORS Where RECORD_TIMESTAMP Between '4/22/2012 12 :00 AM' and '5/9/2012 12 :00 AM' And ( T_E1 Between 10 And 100 OR T_E2 Between 10 And 100 OR T_E3 Between 10 And 100 ) – Sunny May 09 '12 at 12:45

4 Answers4

3

The contextSwitchDeadlock managed debugging assistant (MDA) is activated when a deadlock is detected during an attempted COM context transition.

Symptoms

The most common symptom is that a call on an unmanaged COM component from managed code does not return. Another symptom is memory usage increasing over time.

Cause

The most probable cause is that a single-threaded apartment (STA) thread is not pumping messages. The STA thread is either waiting without pumping messages or is performing lengthy operations and is not allowing the message queue to pump.

Memory usage increasing over time is caused by the finalizer thread attempting to call Release on an unmanaged COM component and that component is not returning. This prevents the finalizer from reclaiming other objects.

By default, the threading model for the main thread of Visual Basic console applications is STA. This MDA is activated if an STA thread uses COM interoperability either directly or indirectly through the common language runtime or a third-party control. To avoid activating this MDA in a Visual Basic console application, apply the MTAThreadAttribute attribute to the main method or modify the application to pump messages.

It is possible for this MDA to be falsely activated when all of the following conditions are met:

· An application creates COM components from STA threads either directly or indirectly through libraries.

· The application was stopped in the debugger and the user either continued the application or performed a step operation.

· Unmanaged debugging is not enabled.

To determine if the MDA is being falsely activated, disable all breakpoints, restart the application, and allow it to run without stopping. If the MDA is not activated, it is likely the initial activation was false. In this case, disable the MDA to avoid interference with the debugging session.

Resolution

Follow COM rules regarding STA message pumping.

To avoid these error popups from appearing, select Exceptions from the Debug menu from Visual Studio window and in the Exception Dialog box select the Managed Debugging Assistants Exception Node. Then select ContextSwitchDeadlock and remove the select from Thrown column.

enter image description here

How to make execution fast:

There are many ways to do it

Create the missing index on table.

Create indexed view on this table.

You can also partition the table on basis of data range.

Put this table on other disk so data read/write will be fast.

Romil Kumar Jain
  • 20,239
  • 9
  • 63
  • 92
  • i had already done this.but this help only to prevent exception not give fast execution for large data.Do you have any other solution to fetch data in small time – Sunny May 09 '12 at 12:24
  • Create the missing index on table or create indexed view on this table. You can also partition the table on basis of data range, put this table on other disk so data read/write will be fast. – Romil Kumar Jain May 09 '12 at 12:25
1

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.

Brett Veenstra
  • 47,674
  • 18
  • 70
  • 86
  • i am using here ChartPlotter to show info on tab2.but i am not able to come on Tab 2 when i select all columns present in listview. :-( – Sunny May 09 '12 at 12:35
  • 170 series on `ChartPlotter` still seems like too much (although http://msdn.microsoft.com/en-us/magazine/ff714591.aspx has an example of 13509 US cities)... Have you tried disabling the control, binding the data and re-enabling? **Where exactly is it timing out?** – Brett Veenstra May 09 '12 at 12:39
  • i am not getting your point here "disabling the control, binding the data and re-enabling".Time out come when i click on view report button to go on tab 2 – Sunny May 09 '12 at 12:49
  • updated answer ... "disabling the control" => look into the `Dispatcher` and `DisableProcessing`. My suspicion is you're hitting this type of problem: http://stackoverflow.com/questions/487661/how-do-i-suspend-painting-for-a-control-and-its-children – Brett Veenstra May 09 '12 at 15:54
  • i am working on BackgroundWorker now and i will let you now is it sufficient for this or not. – Sunny May 10 '12 at 06:50
0

Well, if you want to make your database calls more efficient, I suggest you try out the Enterprise Library, the current version is 5.0. Worked well with our application.

http://msdn.microsoft.com/en-us/library/ff648951.aspx

Sabry
  • 135
  • 2
  • 9
  • when i had started development of this application.I tried to use Enterprise Libery in wpf but wpf didn't support Microsoft.Practices.EnterpriseLibrary.Data :-( – Sunny May 09 '12 at 12:27
0

Personally I suggest that you filter the results at the SQL level instead of in the application itself, although there are several answers here that will also improve your performance on the application side and they should be followed.

For sample code to use SQL paging see T-SQL: Paging with ROW_NUMBER().

Joshua Drake
  • 2,704
  • 3
  • 35
  • 54