Today I helped a customer troubleshoot a SQL Server timeout error in their gallery. It took quite a while to get to the root cause, but once we found it, the fix was ridiculously simple. I'm sharing it here in case it helps others with the issue, since it's related to SQL Server and is not really a Gallery Server issue. This is the error they kept getting: Error: An error occurred while executing the command definition. See the inner exception for details. Event Summary Url http://[redacted]/default.aspx?aid=14 Timestamp 1/27/2016 6:13:54 PM Exception Type System.Data.Entity.Core.EntityCommandExecutionException Message An error occurred while executing the command definition. See the inner exception for details. Source EntityFramework Target Site System.Data.Common.DbDataReader ExecuteStoreCommands(System.Data.Entity.Core.EntityClient.EntityCommand, System.Data.CommandBehavior) at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation) at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() at GalleryServerPro.Business.Factory.AddChildObjects(IAlbum album) at [...]
Long time fans of Gallery Server may recall that versions earlier than 2.5 supported SQLite as a database option. At the time it was a great choice because it was a file-based database that was robust, fast, required no extra software on the server, and required no maintenance. The only real downsides were that we had to distribute both a 32-bit and 64-bit version, and it ran only in Full Trust environments (no worky on GoDaddy). When Microsoft released SQL CE 4, we realized it would solve both of these issues, so in 2.5 we dropped SQLite support and moved to SQL CE. Overall it's been a great move - it just "works" and people who use it almost never have to interact with the underlying database. But it came with a price. It is slow. So slow that customers who use SQL Server often see a 20-fold increase in database performance. If you're running [...]
Today I identified a bug in the SQL Server provider where it does not correctly handle roles that have been assigned to multiple albums. The package downloads have been updated, so if you downloaded 2.5.0 before today, grab the latest GalleryServerPro.Data.SqlServer.dll from the install or upgrade package and replace your version.