Notice: In order to edit this ticket you need to be either: a Product Owner, The owner or the reporter of the ticket, or, in case of a Task not yet assigned, a team_member"

Task #4200 (closed)

Opened 9 years ago

Closed 8 years ago

Too many queries made on DB UPDATE

Reported by: jamoore Owned by: jamoore
Priority: critical Milestone: OMERO-Beta4.3
Component: Performance Version: n.a.
Keywords: n.a. Cc:
Resources: n.a. Referenced By: n.a.
References: n.a. Remaining Time: 0.0d
Sprint: 2011-05-19 (12)

Description (last modified by jmoore)

While stepping through the very slow tag update (#3978) some queries could not be immediately removed:

...ServiceHandler] (l.Server-9)  Meth:	interface ome.api.IUpdate.saveAndReturnObject
...ServiceHandler] (l.Server-9)  Args:	[ome.model.annotations.TagAnnotation:Id_390]
...util.SqlAction] (l.Server-9) ome.util.actions.PostgresSqlAction@2eb481ba.deferConstraints([])

## These are the loading of the context: Experimenter, Group, Session, EventType
....hibernate.SQL] (l.Server-9) select experiment0_.id as id114_0_, experiment0_.external_id as external10_114_0_, experi
....hibernate.SQL] (l.Server-9) select experiment0_.id as id37_0_, experiment0_.description as descript2_37_0_, experimen
....hibernate.SQL] (l.Server-9) select session0_.id as id60_0_, session0_.closed as closed60_0_, session0_.defaultEventType
....hibernate.SQL] (l.Server-9) select this_.id as id42_0_, this_.external_id as external4_42_0_, this_.permissions as permis

## Creating the event
....hibernate.SQL] (l.Server-9) insert into event (containingEvent, external_id, permissions, experimenter, experimenterGro
...c.EventHandler] (l.Server-9)  Auth:	user=2,group=3,event=302(User),sess=4e4d1262-1376-4733-87cf-044c1615a3d2

## Checking what has changed
....hibernate.SQL] (l.Server-9) select tagannotat0_.id as id13_0_, tagannotat0_.description as descript3_13_0_, tagannotat0

## Reloading the old events in the annotation
....hibernate.SQL] (l.Server-9) select event0_.id as id117_36_, event0_.containingEvent as containi5_117_36_, event0_.exte
....hibernate.SQL] (l.Server-9) select event0_.id as id117_36_, event0_.containingEvent as containi5_117_36_, event0_.exte

## Reloading the annotation's links
....hibernate.SQL] (l.Server-9) select annotation0_.parent as parent13_1_, annotation0_.id as id1_, annotation0_.id as id56

## Reloading node??
....hibernate.SQL] (l.Server-9) select node0_.id as id99_0_, node0_.conn as conn99_0_, node0_.external_id as external9_99

## The actual change
....hibernate.SQL] (l.Server-9) update annotation set description=?, group_id=?, owner_id=?, permissions=?, update_id=?, ns=?, version=?, textValue=? where id=?
...CurrentDetails] (l.Server-9) Adding log:UPDATE,class ome.model.annotations.TagAnnotation,390
...util.SqlAction] (l.Server-9) ome.util.actions.PostgresSqlAction@6c908f05.nextValue([seq_eventlog, 1])
...util.SqlAction] (l.Server-9) ome.util.actions.PostgresSqlAction@2eb481ba.insertLogs([[[Ljava.lang.Object;@3b1c11ec]])
...j.TimingLogger] (l.Server-9) start[1296574174709] time[110] tag[omero.call.success.ome.logic.UpdateImpl.saveAndReturnObject]
...ServiceHandler] (l.Server-9)  Rslt:	ome.model.annotations.TagAnnotation:Id_390

Change History (5)

comment:1 Changed 9 years ago by jmoore

  • Description modified (diff)

comment:2 Changed 8 years ago by jmoore

  • Sprint set to 2011-05-19 (12)
  • Status changed from new to accepted

comment:3 Changed 8 years ago by jmoore

  • Remaining Time set to 0.5

comment:4 Changed 8 years ago by jmoore

By checking for events which are immutable in MergeEventListener, we should be able to remove these queries:

## Reloading the old events in the annotation
....hibernate.SQL] (l.Server-9) select event0_.id as id117_36_, event0_.containingEvent as containi5_117_36_, event0_.exte
....hibernate.SQL] (l.Server-9) select event0_.id as id117_36_, event0_.containingEvent as containi5_117_36_, event0_.exte

## Reloading node??
....hibernate.SQL] (l.Server-9) select node0_.id as id99_0_, node0_.conn as conn99_0_, node0_.external_id as external9_99

comment:5 Changed 8 years ago by jmoore <josh@…>

  • Remaining Time changed from 0.5 to 0
  • Resolution set to fixed
  • Status changed from accepted to closed

(In [6899ad144b9a848513aa5b2eab4dbf3b625c160c/ome.git] on branch develop) Proxying Events in MergeEventListener (Fix #4200)

In rough tests, this sped a saveAndReturnObject call up
from 0.15 seconds on average to 0.05.

Note: See TracTickets for help on using tickets. You may also have a look at Agilo extensions to the ticket.

1.3.13-PRO © 2008-2011 Agilo Software all rights reserved (this page was served in: 0.79448 sec.)

We're Hiring!