Sometimes when I call IContentRepository.Save(...) I get an SQL timeout. I can only assume that this is due to a large product database with a number of meta fields, which leads to a huge MetaKey-table. The call will time out after 30 seconds, but I would like to increase this. Where can I find this setting?
I'm using EPiServer Commerce 8.11.
Okay, so I can see that it's actually MetaDataCommiter.CommitMetaAttributes(...) and then MetaObject.AcceptChanges(...) that "triggers" the timeout. In MetaObject.AcceptChanges(...) we can see a call to MetaDataPlusDatabase.UpdateMetaObject(...) which in its turn calls MetaDataPlusDatabase.StoredProcedure(...).ExecuteNonQuery(...). The last step is to call DBHelper.ExecuteNonQuery(...) and this is "where the fun ends". It seems there is no way to set the command timeout.
Unfortunately IContentRepository.Save<EntryContentBase> is a big operation which involves both CMS and Commerce databases. You'll need to make sure which database is actually timeout.
If it's on Commerce side then one possible solution is to create a class inherit from SqlDataProvider which adjust the CommandTimeOut in DataCommand to the value you need, then register it in web.config/FrameworkProviders as the default provider for dataService.
The good news is we are working on a new version (possibly Commerce 9) which drastically improve the performance of IContentRepository.Save. After that the solution above should no longer be needed.
Inheriting SqlDataProvider is probably exactly what I need!
Trying it out just using this code now...
public class ClientSqlDataProvider : SqlDataProvider
public override DataResult ExecuteNonExec(DataCommand command)
command.CommandTimeout = CatalogConfiguration.Instance.Connection.CommandTimeout;
... and registering in FrameworkProviders just as you suggest. It seems to do the job!
You, my friend, is a true hero! The hero that Gotham deserves!