IPojos.getUserImages (and others?) is very slow

[jamoore]

Jason:

One thing here, that I have discussed with various folks here.
Queries to other user's Dataset's and Images are VERY VERY slow.
Maybe easiest to see this on a loaded server-- see mage (login as me,
then switch to say Iain's data). In DataManage?, open a Project.
Fine. Open a Dataset (with 6 images). Go make coffee. Rendering
seems OK (but this needs to be profiled). If you watch the server,
it's all postgres. I know Chris and jean-Marie are aware of this,
but have jst be aware and just make sure something is not biting or bum.

Josh:

Ok. I'll certainly take a look at the movie, but just to get me
started, how long does it take to make coffee in this case? I just
looked at Iain's images, and though it was certainly long (in 4 cases
7 seconds, and in 3, 8 seconds via VMware/VPN/Germany), I couldn't
have made a coffee. (Though I don't have very much practice with that,
AND I'm home alone with no one to help me, so.... :) )

Could it be a particular dataset?

Josh:

To make this even more fun, Emma's datasets all load in 3 to 4
seconds with more images than Iain's (which are still ~7). J

P.S. Thought I'd try somemore:

Michael ~ 20 seconds !!!
Elena ~ 3 seconds
David & Cat ~ 1 second
self (Jason) ~ 3 seconds

OMERO group:

Jean-Marie ~ 2 seconds
Brian ~ 5 seconds

This is regardless of dataset size and in what order I test them
(i.e. memory's not filling up or something). Most likely it has to do
with the size of a table that this stuff is joining against. The query
is:

select [STUFF]

from image this_ 
  inner join event create1_ on this_.creation_id=create1_.id 
  inner join event update2_ on this_.update_id=update2_.id 
  left outer join pixels pixels3_ on 'true'=pixels3_.defaultPixels and this_.id=pixels3_.image and PERMISSIONS
  left outer join pixelsdimensions pixelsdime5_ on pixels3_.pixelsDimensions=pixelsdime5_.id 
  left outer join pixelstype pixelstype4_ on pixels3_.pixelsType=pixelstype4_.id 
  left outer join datasetimagelink genlink_1x7_ on this_.id=genlink_1x7_.child and PERMISSIONS
  left outer join dataset genitem_1x6_ on genlink_1x7_.parent=genitem_1x6_.id
 where 
( 
 PERMISSIONS
)
 and ? = this_.owner_id and genitem_1x6_.id in (?) limit ?

so if we look at those tables:

 michael
 dataset  |    27
 datasetimagelink |  1076
 pixels   |  1229
 imgs     |  1229
 dims     |  1229
 ----
 iain
 dataset  |    36
 datasetimagelink |   313
 pixels   |   326
 imgs     |   326
 dims     |   326
 ----
 brian
 dataset  |    21
 datasetimagelink |   268
 pixels   |   255
 imgs     |   255
 dims     |   255
 ----
 elena
 dataset  |     6
 datasetimagelink |    63
 pixels   |   108
 imgs     |   108
 dims     |   108
 ----

[jburel]

Same problem with Ipojos.getImages()

[jmoore]

Methods removed from Beta2.2. Investigating solution, may need to be moved to Beta3.

[jmoore]

Significant improvements seen, but nothing quantitative. Pushing.

[jmoore]

CollectionsCounts? should have significantly changed this. Closing.

[jmoore]

"CollectionCounts". Sorry.