EffiProz Connection Management

Jan 3, 2011 at 10:12 PM

Hi,

I'd like to use EffiProz as a simple visitor logging database for a personal website and I'm unsure as to the correct connection usage model.

Should I open a new connection every time I want to access the database? I tried this and only achieved about 1 insert/second.

I got much better performance by holding a single connection open - but then if my web app isn't shut down cleanly then I have "uncommitted" transactions in the log file. I see they get played forward when I next access the database - so not such a bad situation.

I did read a post on the Effiproz blog about performance which says "we shutdown the database after all inserts and re-opened before doing other operations" - I'm not sure if this means the connection was closed between each insert or something else...?

Many thanks,

Richard

Coordinator
Jan 4, 2011 at 12:04 AM

Hi,

Always keep the connection open as long as possible. But never share the connection between threads.

If you need to close and reopen a connection multiple times then set "auto shutdown= false" in connection string. This would give better performance.

To get rid of transaction log you can execute "SHUTDOWN" command (or call EfzConnection.Shutdown) on APPLICATION_END event.

-thanks

 

Jan 5, 2011 at 6:02 PM

Thanks very much - the "auto shutdown" was exactly what I needed - extremely fast now.

There does seem to be a subtle thread safety issue when driving the database very hard (3 insert threads and 1 read thread). Let me know if you want the source code to reproduce...

System.Data.EntityException was unhandled
  Message=The underlying provider failed on Open.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
       at System.Data.EntityClient.EntityConnection.Open()
       at System.Data.Objects.ObjectContext.EnsureConnection()
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__3[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
       at tmpMemDbTest.Program.ReadThread() in C:\tmpMemDbTest\tmpMemDbTest\Program.cs:line 64
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.IndexOutOfRangeException
       Message=Index was outside the bounds of the array.
       Source=mscorlib
       StackTrace:
            at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
            at EffiProz.Core.Engine.Database.Connect(String username, String password, String zoneString, Int32 timeZoneSeconds)
            at EffiProz.Core.Engine.DatabaseManager.NewSession(String type, String path, String user, String password, EffiProzProperties props, String zoneString, Int32 timeZoneSeconds)
            at System.Data.EffiProz.EfzConnectionProxy.Open()
            at System.Data.EffiProz.EfzConnection.Open()
            at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
       InnerException:

Coordinator
Jan 6, 2011 at 12:29 AM

Thanks.

Yes, Could you please send the source code to support@effiproz.com