Ticket #657 (closed defect: fixed)
IPojos.getUserImages (and others?) is very slow
| Reported by: | jmoore | Owned by: | jmoore |
|---|---|---|---|
| Priority: | critical | Milestone: | 3.0-Beta3 |
| Component: | Queries | Version: | 3.0-M3 |
| Keywords: | performance | Cc: | jason, jburel |
Description
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 ----
