locked
Query TimeOut - anyway to increase value? RRS feed

  • Question

  • Hi,

    I have a table based on a view in sql server, approx. 250K records,

    The query for the search screen connected to that table is taking longer than 30 seconds, hence I am getting unable to load data - check your network connection.

    increasing the connection timeout in the web.config has no effect - is there a way to increase the default query timeout of 30 secs?

    thx

    MrP

    Friday, August 15, 2014 4:16 PM

All replies

  • I don't know if there is a way to change that particular timeout, but I have a couple of other suggestions:

    Did you add a query to the LightSwitch entity to limit the number of records returned? (Are you returning 250,000 records to the client.)

    Do you have indexes on the database table(s)?

    How long does the view take to run in SQL Server Management Studio?

    Did you know that if you publish changes to your database that the publishing process will delete any indexes that you have built? (And you have to add them again manually.)

    Mark

    Sunday, August 17, 2014 7:49 PM
  • i only return data if parameters are supplied, in the preprocess check for parameters etc.

    yes there are indexes on the tables, if i call the view in sql it can take 35 seconds to bring back 250K, however if i supply some parameters to the sql the view is based on ~ 7-10 seconds is the normal response.

    sometimes the screen works just fine and i get the 10 second response, and then sometimes it times out - not sure how lightswitch is interacting with the view - i believe it should only be bringing back the filtered data.

    MrP

    Monday, August 18, 2014 3:03 PM
  • The only solution I found was to drop back to Entity Framework's object context, still using the integrated LS model. EF supports a command timeout property.

    //Using EF lets us specify the .Includes to eager-load our required data, in a single roundtrip!
    // EF also lets us access the FK attributes hidden by the Lightswitch entity objects.
    using (LightSwitchApplication.Implementation.RealEstateData objectContext =
                    new LightSwitchApplication.Implementation.RealEstateData(this.EFConnectionString))
    {
      objectContext.CommandTimeout = 90;
      this.BuildingDeposits = objectContext.BankDeposits
        .Include("Payments.PaymentAllocations")
        .Where(d => d.BankAccount.buildingId == buildingId && d.depositDate >= this.StartDate && d.depositDate <= this.EndDate)
        .OrderBy(d => d.depositDate)
        .ToList();
    }

    The entity framework connection string can be generated from the LS connection.

    /// <summary>
    /// A connection string to instantiate the underlying Entity Framework ObjectContext.
    /// e.g., var oc = new LightSwitchApplication.Implementation.RealEstateData(builder.ConnectionString);
    /// </summary>
    public string EFConnectionString
    {
      get
      {
        //SAMPLE: "metadata=res://Application.Server/RealEstateData.csdl|res://Application.Server/RealEstateData.ssdl|res://Application.Server/RealEstateData.msl;provider=System.Data.SqlClient;provider connection string=\"Data Source=.;Initial Catalog=RealEstate;Integrated Security=True\"";
        string dbConnectionString = WebConfigurationManager.ConnectionStrings["RealEstateData"].ConnectionString;
    
        System.Data.EntityClient.EntityConnectionStringBuilder builder = new System.Data.EntityClient.EntityConnectionStringBuilder();
        builder.Metadata =
          "res://Application.Server/RealEstateData.csdl|res://Application.Server/RealEstateData.ssdl|res://Application.Server/RealEstateData.msl";
        builder.Provider = "System.Data.SqlClient";
        builder.ProviderConnectionString = dbConnectionString;
    
        return builder.ConnectionString;
      }
    }


    Paul

    Monday, August 18, 2014 9:17 PM
  • Where does this code reside?
    Thursday, August 21, 2014 8:52 PM
  • The code is used instead of a Lightswitch query. You would have to assign the returned results to the display control.

    Paul

    Thursday, August 21, 2014 11:48 PM
  • Paul,

    the screen i have has about 4-5 search parameters - if i add a button to initiate the search - i'm thinking the button click could maybe have this code.

    or could you do this in the query preprocess? 

    Thanks

    MrP

    Friday, August 22, 2014 2:50 AM
  • The button click sounds like the right place. Once you're writing your own data access code, you might also want to write a SQL stored procedure and see if calling that is faster than either Lightswitch or Entity Framework.

    Paul

    Friday, August 22, 2014 12:07 PM
  • I had the object context code all setup but i was unable to extend the timeout - not exactly sure why

    I ended up creating a wcf service to call a stored procedure, it's a lot faster, and adding that as a data source.

    thanks for you help

    mrP

    Tuesday, August 26, 2014 2:59 AM
  • I had trouble with this, as well.  Here is what I found worked for me in Visual Studio 2015.

    In the Web.config of the server application, there was an appSettings section in configuration:

    <configuration>
      <appSettings>

     <!-- Lots of settings here -->

      </appSettings>

     <!-- Lots of other stuff here -->

    </configuration>

    Inside of this section, there were already various add key tags, including this one:

        <add key="ContainerData_CommandTimeout" value="0" />

    I don't remember how this got there, but "ContainerData" is the name of one of my data sources.  The data source I was having problems with was called "DEACTPerDiemsData", so I added this line to that section:

    <add key="DEACTPerDiemsData_CommandTimeout" value="0" />

    This worked.  Long running queries against this data source which previously timed out after 30 seconds now complete.

    I suspect there has to be UI somewhere to set this, as well; the timeout for ContainerData got set somehow and I don't remember doing it this way.

    • Proposed as answer by Andrew Bennett Thursday, March 17, 2016 2:52 PM
    Thursday, March 17, 2016 2:51 PM