SQL Command Times out after 30 seconds

Hi,
I have a nopcommerce 2.3 setup and it has about 7000 products and some complex queries (with filters) need more time than 30 seconds to execute. I was wondering where and how I can change the sql command timeout to 60 seconds.

Have a look at the latest version. The stored procedure used for product searching is MUCH faster now (tested on million products)

Sorry, update to the latest version is not an option, I'm looking to edit the command timeout at the version I have set up the client (they are working on it for several months now).

Is the new stored proc compatible with v2.3?

No. You'll have to modify it a bit. Make some investigation (remove new parameters, etc)

georgemak wrote:


Were you able to increase the timeout?
I am facing the same issue query execution takes time(50- 60sec) and application returns timeout.

Sorry to reignite an old post, but it seems the answer was missing for some people. Please see below.

You can increase the timeout within Nop.Data > EfRepository.cs

We had issues with an INSERT timing out so we updated the Insert and Update methods.

Note: This was for version 3.5

public virtual void Insert(T entity)
        {
            try
            {
                if (entity == null)
                    throw new ArgumentNullException("entity");

                // DBG Added - Increase timeout for inserting into the database
                var previousTimeout = ((DbContext) this._context).Database.CommandTimeout; 
                ((DbContext) this._context).Database.CommandTimeout = 120;

                this.Entities.Add(entity);

                this._context.SaveChanges();

                // DBG Added - Revert timeout back to its original value
                ((DbContext) this._context).Database.CommandTimeout = previousTimeout;
            }
            catch (DbEntityValidationException dbEx)
            {
                throw new Exception(GetFullErrorText(dbEx), dbEx);
            }
        }

public virtual void Update(T entity)
        {
            try
            {
                if (entity == null)
                    throw new ArgumentNullException("entity");

                // DBG Added - Increase timeout for updating objects in the database
                var previousTimeout = ((DbContext) this._context).Database.CommandTimeout; 
                ((DbContext) this._context).Database.CommandTimeout = 120;

                this._context.SaveChanges();

                // DBG Added - Revert timeout back to its original value
                ((DbContext) this._context).Database.CommandTimeout = previousTimeout;
            }
            catch (DbEntityValidationException dbEx)
            {
                throw new Exception(GetFullErrorText(dbEx), dbEx);
            }
        }

Long running queries are always failing at 30 seconds, even when the CommandTimeout parameter is 0 or greater than 30.
SO, Do below steps for Optimize the database.

1.Remove the unused index.
2. Removing the Redundant Indexes.
3. Shrink the database.

To set CommandTimeout, you can check at https://stackoverflow.com/questions/10549640/how-to-set-commandtimeout-for-dbcontext

Long running queries are always failing at 30 seconds, even when the CommandTimeout parameter is 0 or greater than 30.
SO, Do below steps for Optimize the database.

1.Remove the unused index.
2. Removing the Redundant Indexes.
3. Shrink the database.

To set CommandTimeout, you can check at https://stackoverflow.com/questions/10549640/how-to-set-commandtimeout-for-dbcontext