locked
IS NOT NULL Causes Timeout RRS feed

  • Question

  • I have a view that joins about 6 tables the first of which has null values in a column that is filtered.

    The view returns results using Linq to Entities just fine until I say WHERE personID IS NOT NULL. If I do that, the IQueryable gives a Command Timeout. I can query the view with the IS NOT NULL in SSMS, I can query an identicle database schema with different data on the same machine just fine. It only fails when I add IS NOT NULL, using Linq to Entities and only on one particular database.

    Has anyone seen this?


    Eric Kleeman Senior Dev Engineer Inovalon

    Tuesday, July 17, 2012 2:49 PM

All replies

  • If the query is too complex, the recommended way is to execute directly in EF, please try to check which process takes most time.

    Go go Doraemon!

    Thursday, July 19, 2012 8:16 AM
  • Hi Eric,

    Do you mean only query on this view will lead timeout, and other views works fine? If yes, please try to check the generated T-SQL by the Linq to Entities, like @Dorado999 said, if the T-SQL is too complex, please execute your own T-SQL in Entity Framework directly and check whether it works.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, July 20, 2012 6:11 AM
  • It works on all our databases except one. There is something about one particular database that causes a command timeout when IS NOT NULL is added to the where clause. And, to make things worse, we cannot use Linq because the view cuts across multiple database instances. Until Linq to EF supports querying multiple contexts, using Linq is not possible.

    Eric Kleeman Senior Dev Engineer Inovalon

    Friday, July 20, 2012 1:25 PM
  • Hi Eric Kleeman,

    I will involve my workmates to research on this issue, it may need some time, thanks for your understanding. : )

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 23, 2012 7:23 AM
  • Hi Eric,

    How about creating a stored procedure and use EF to call the stored procedure directly? 

    http://weblogs.asp.net/kencox/archive/2009/11/25/using-a-stored-procedure-in-entity-framework-4.aspx

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, July 24, 2012 7:06 AM
  • That's a possibility, however we would like to know why it is causing an error in just one database instance.

    In our architecture, we prefer using EF, and resort to using views when it makes sence and access that data with EF. We do use a lot of procs for SSRS but they add another layer to maintain when you are deploying schema changes every few weeks. And, at the end of the day, when you build an IQueryable you are essentially creating a stored procedure under the hood, but no parameters to maintain and no code in the database project.

    Having said that procs would make sence if we decide to build a persistence layer datastor to maintain a cache of data that cuts across database instances. Then access that with EF.


    Eric Kleeman Senior Dev Engineer Inovalon

    Tuesday, July 24, 2012 1:25 PM
  • Hi Eric,

    To troubleshot why it cause the timeout exception, I would recommend you use SQL Server Profiler to check the SQL query being used.  Then you may try to directy execute the query through regular SqlCommand.   If SqlCommand works fine, we can narrow down the issue is at the EF side.  

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 25, 2012 1:45 AM