none
What could cause the call to this SPROC to timeout while compiling. RRS feed

  • Question

  • We have a WCF service that is calling a SQL Server sproc that is timing out on the initial call. It appears the problem is that it is taking forwever to compile. The same SPROC called from an ASP.NET web app using classic SqlConnection/SqlCommand methods compiles almost instantly. I am wondering, what could be different about the WCF call that could cause the compile to be extremely slow.

    Example code is as follows:

     

    public class CompanyDB : DataContext
    {
        [Function(Name="usp_Products_Submitted")]
        public ISingleResult<Product> ListProducts(
           [Parameter(Name = "SortColumn", DbType = "Int")] int sortColumn,
    	[Parameter(Name = "SortMode", DbType = "Int")] int sortMode,
    	[Parameter(Name = "UserID", DbType = "NVarchar(25)")] string userId,
    	[Parameter(Name = "Date_From", DbType = "NVarchar(10)")] string dateFrom,
    	[Parameter(Name = "Date_To", DbType = "NVarchar(10)")] string dateTo)
    	{
    	    IExecuteResult result = this.ExecuteMethodCall(this,
    		((MethodInfo)(MethodInfo.GetCurrentMethod())),
    		sortColumn, sortMode, userId, dateFrom, dateTo);
               return (ISingleResult<Product>)result.ReturnValue;
    	}
    }
    
    public List<Product> GetProductsList(string userId, string start, string end)
    {
        using (var db = new CompanyDB(Database.Conn))
        {
            return = db.ListProducts(17, 0, userId, start, end).ToList();
        }
    }
    
    

    I am at a loss as to where to look.

     




    • Edited by Bill2010 Friday, January 20, 2012 11:17 PM
    Friday, January 20, 2012 11:00 PM

Answers

  • Hi,

    I agree that it's quite strange. Is it possible to create some repro project for us to do further investigation?   You may directly ping me at misun@microsoft.com.   Also, how does the db team modify the stored procedure? 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by Bill2010 Thursday, January 26, 2012 3:47 PM
    Thursday, January 26, 2012 5:32 AM
    Moderator

All replies

  • Hi Bill,

    So the problem here is that the compile time is very slow and you have not actually executed the app, right? 

    Please enable the Diagnostic of the VS build verbosity, http://blogs.msdn.com/b/msbuild/archive/2005/09/29/475157.aspx, to track what's going on during the compile time.  

    You can provide us with the build logs if it's convenient for you.   You may directly ping me at misun@microsoft.com.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 23, 2012 3:11 AM
    Moderator
  • What the DB team is seeing is that it is the SPROC call that is slow to compile when called by our L2E WCF app. It is timing out. Once compiled, however, it runs very quickly. This only happens with the WCF L2E service and does not occur when being called by our legacy web applications or when executed directly in SQL Server Managment Server.

    It is not clear why a call from the WCF service would be any different.

    Monday, January 23, 2012 3:29 PM
  • Hi,

    Sorry, I still cannot quite understand why the compile time here is very slow.   Are you using LINQ to SQL to call stored procedures in a WCF service?  When you mentioned L2E, do you mean LINQ to Entities?  

    Also, do you mean the SPROC call is slow to compile at the database side?  Please provide us with more detailed information.  

    Good day!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, January 24, 2012 6:46 AM
    Moderator
  • Sorry, I meant to say I am using LINQ to SQL to call the stored procedure. The db team modified the sproc and that seems to have helped with the initial compile. What was unclear is why the compile was taking so long when called from L2S. It should be standard SQL, nothing mysterious.
    Tuesday, January 24, 2012 3:15 PM
  • Hi,

    I agree that it's quite strange. Is it possible to create some repro project for us to do further investigation?   You may directly ping me at misun@microsoft.com.   Also, how does the db team modify the stored procedure? 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by Bill2010 Thursday, January 26, 2012 3:47 PM
    Thursday, January 26, 2012 5:32 AM
    Moderator
  • The DBA team refactored the sproc and, so far, it seems to be working. They are still not sure why we were seeing the behavior we were seeing.

    I want to thank you for your offer of assistance.

    Thursday, January 26, 2012 3:49 PM