Transactions mandatory - Having problems with performance

[Leonardo]

Hi all,

I've been reading the posts here concerning transactions mandatory. I
really didn't know this until recently, as I've only used transactions
when writing something, but I decided to implement this in the project
I'm working on. The problem is that when I did this, I transformed a
400ms request to a 800ms request (this request only does a single
query), so that is a big performance issue. Why is this? I have the DB
located in USA, where as my dev environment is in Argentina. Could
that be the problem? How can I solve this so as to use transactions
even for reading?

Any further suggestions on how to apply this? Things such as isolation
level or something to leverage the round trip?

Any thoughts?

Thanks to everyone in advance!

Bye!

Leonardo

[Dietrich]

Leonardo,

I've noticed less overhead when implementing transaction management in
my DAL. It may help us to help you if you posted a sample of your code
where you're using transactions.

Thanks!

[Leonardo]

I'm using ASP.NET MVC and I've created a TransactionAttribute action
filter that opens a transaction in the method OnActionExecuting and
commits in OnResultExecuted. If any error appears, in OnError I
rollack the transaction. So, the transaction is alive in the entire
action and in the view rendering (I do this in case I call a lazy
property in the view so that query can also be inside the
transaction).
The structure of a method in the controller looks like this:

[Transaction]
public ActionResult DoOnlyQuerying()
{
//Call some method that performs a query. (Transaction is
already open here).
return View(someEntity); //Transaction is still open when
rendering the view.
}

While debugging, I noticed that most of the time spent in the request
is done in the BeginTransaction method, not in the queries.

> > Leonardo- Hide quoted text -
>
> - Show quoted text -

[John Davidson]

Is it possible that you are rebuilding your NHibernate SessionFactory in each BeginTransaction. That would account for the added time in this manner.

John Davidson

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Fabio Maulo]

IMO the real problem is another.

A developer shouldn't use a DB-server hosted in another country during development and even worst if the DB-server is used for testing by others developers..

2010/2/10 John Davidson <jwdav...@gmail.com>

--
Fabio Maulo

[Leonardo]

To John: I have an object that is singleton (using an IoC container)
that configures the sessionFactory once, so that is not the problem.

To Fabio: I know this is not the best dev environment, but if that is
the only problem and when deployed in the CI server everything will be
OK, then i can handle this delay during development.

Is that the only inconvenience you find in this situation?

On Feb 10, 4:35 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> IMO the real problem is another.
> A developer shouldn't use a DB-server hosted in another country during
> development and even worst if the DB-server is used for testing by others
> developers..
>

> 2010/2/10 John Davidson <jwdavid...@gmail.com>

>
>
>
>
>
> > Is it possible that you are rebuilding your NHibernate SessionFactory in
> > each BeginTransaction. That would account for the added time in this manner.
>
> > John Davidson
>

> >> nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com­>

> >> .
> >> For more options, visit this group at
> >>http://groups.google.com/group/nhusers?hl=en.
>
> >  --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com­>

> > .
> > For more options, visit this group at
> >http://groups.google.com/group/nhusers?hl=en.
>
> --

> Fabio Maulo- Hide quoted text -

[Robert Rudduck]

Is it possible that for some reason your transaction is getting promoted to a distributed transaction?

The call to BeginTransaction should be very fast, as NHibernate doesn't really do any real work until it actually goes to the DB. Is it possible that this delay was just a temporary disruption?

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

[Fabio Maulo]

Good point Robert.

Leonardo, are you using NH2.1.2 or previous ?

2010/2/10 Robert Rudduck <rob...@rpowered.net>

--
Fabio Maulo

[Leonardo]

I'm using NHibernate 2.1.2GA.
The way I open the transaction is by setting session's flush mode to
automatic (i set it to mode never when a session is opened, so it
doesn't flush anything unless inside a transaction), and then using
BeginTransaction() without parameters, so IsolationLevel.Unspecified
(I suppose) is used.

On 10 feb, 18:57, Fabio Maulo <fabioma...@gmail.com> wrote:
> Good point Robert.
> Leonardo, are you using NH2.1.2 or previous ?
>
> 2010/2/10 Robert Rudduck <rob...@rpowered.net>
>
>
>
>
>
> > Is it possible that for some reason your transaction is getting promoted to
> > a distributed transaction?
>
> > The call to BeginTransaction should be very fast, as NHibernate doesn't
> > really do any real work until it actually goes to the DB. Is it possible
> > that this delay was just a temporary disruption?
>

> >> <nhusers%2Bunsu...@googlegroups.com<nhusers%252Bunsubscribe@googlegroup s.com>

> >> ­>
> >> > >> .
> >> > >> For more options, visit this group at
> >> > >>http://groups.google.com/group/nhusers?hl=en.
>
> >> > >  --
> >> > > You received this message because you are subscribed to the Google
> >> Groups
> >> > > "nhusers" group.
> >> > > To post to this group, send email to nhu...@googlegroups.com.
> >> > > To unsubscribe from this group, send email to
> >> > > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com >

> >> <nhusers%2Bunsu...@googlegroups.com<nhusers%252Bunsubscribe@googlegroup s.com>

[Leonardo]

I don't think it's a temporary disruption, as it repeatedly continues
to delay approximately 800 ms.
One thing to say is that the session opening is "lazy", what I mean is
that unless anyone requests a session, it is not opened. The first
place in the request where the session is opened is when the
transaction opening requires it. But in the case without transactions,
the same happens, so the session is opened when the query is executed,
so both approaches should have the same "session opening" delay.
Something when I use transaction is making that 400ms extra delay.
It really doesn't immediately call a begin transaction in the DB? I
didn't profiled that to be sure, I'll do that tomorrow when I get to
work.

[kor]

are you getting the 400->800 change also with a single request (i mean
without concurrent request)?

[Leonardo]

Yeah, myselft testing it alone makes that increase in time.

[Leonardo]

I've measured the time elapsed to open a transaction, it is taking
about 200ms to open it. Are you sure it isn't going immediately to
open the transaction in the db? Is there somewhere to configure that?

These are the measures I took:

With transaction:
DEBUG - Open session elapsed: 29 ms
DEBUG - Begin (Unspecified) <----------- This looks like going to the
db to open the transaction
DEBUG - Obtaining IDbConnection from Driver <----------- Together with
this
DEBUG - Transaction opening elapsed: 199 ms
DEBUG - Getting object elapsed: 406 ms

Without transaction:
DEBUG - Open session elapsed: 0 ms
DEBUG - Getting object elapsed: 207 ms

With transaction:
DEBUG - Open session elapsed: 0 ms
DEBUG - Begin (Unspecified)
DEBUG - Obtaining IDbConnection from Driver
DEBUG - Transaction opening elapsed: 196 ms
DEBUG - Getting object elapsed: 201 ms

Without transaction:
DEBUG - Open session elapsed: 7 ms
DEBUG - Getting object elapsed: 209 ms

With transaction:
DEBUG - Open session elapsed: 0 ms
DEBUG - Begin (Unspecified)
DEBUG - Obtaining IDbConnection from Driver
DEBUG - Transaction opening elapsed: 195 ms
DEBUG - Getting object elapsed: 202 ms

Without transaction:
DEBUG - Open session elapsed: 0 ms
DEBUG - Getting object elapsed: 203 ms

With transaction:
DEBUG - Open session elapsed: 0 ms
DEBUG - Begin (Unspecified)
DEBUG - Obtaining IDbConnection from Driver
DEBUG - Transaction opening elapsed: 195 ms
DEBUG - Getting object elapsed: 201 ms

Any thoughts?

[kor]

opening a session will not open a connection so no db access (the
connection will be opened only when required),
begin a nhibernate transaction will sudden open a connection to the
db,
but opening the connection before or later will not influence the
total time.

are u using the 2-level cache?

[Leonardo]

The session opening time is not important, what's important is the
time to begin a transaction.

I'm not using 2-level cache, but i'll do that in the future.

[Leonardo]

So kor you think the problem is the roundtrip to open a connection
when the begin transaction is executed?
That will lead us back to the problem of the distance from the DB.

[Leonardo]

I've just seen the source of NHibernate, and it IS actually calling a
begin transaction from the IDbConnection:

Extracted from the AdoTransaction Begin method:

try
{
if (isolationLevel == IsolationLevel.Unspecified)
{
isolationLevel = session.Factory.Settings.IsolationLevel;
}

if (isolationLevel == IsolationLevel.Unspecified)
{
trans = session.Connection.BeginTransaction();
}
else
{
trans = session.Connection.BeginTransaction(isolationLevel);
}
}
catch (HibernateException)
{
// Don't wrap HibernateExceptions
throw;
}
catch (Exception e)
{
log.Error("Begin transaction failed", e);
throw new TransactionException("Begin failed with SQL exception",
e);

[kor]

On Feb 11, 5:07 pm, Leonardo <leoa...@gmail.com> wrote:
> So kor you think the problem is the roundtrip to open a connection
> when the begin transaction is executed?
> That will lead us back to the problem of the distance from the DB.
>

i'm not so good in databases but i think that your problem is that, if
it's only a single query and the time overhead is a problem don't uses
explicit transaction

[Leonardo]

Thanks kor, I run the same code in the same place where the DB is, and
the time to open the transaction was ranging from 4 ms (first time in
application) to 0 ms (second time and after). So that was the problem
after all!

Thanks everyone for your thoughts.