I'm trying to use LABKEY.query.getQueryDetails() on the participant table of a study. LABKEY.query.getQueryDetails() runs reasonably fast on most tables, but not participant. In the participant table, I have aliased the participant field 10 times, each of these being a lookup to another query. Because getQueryDetails() returns the structure of any lookups, am I correct that this probably explains the slowness I see. Basically I've asked getQueryDetails() to do 10X as much work, right?
I did not see anything documented, but can I run getQueryDetails() with a view specified so that I exclude columns I don't need (and their associated lookups)?
There's some overriding EHR study performance issues and maybe this is just one manifestation. |
| |
| daves responded: |
2010-03-11 10:31 |
Hi Ben,
The schema browser calls getQueryDetails when you view a particular query, so do you notice that the schema browser also take longer to display the details of the study.Participant as well? I wouldn't expect it to be all that much longer if you have 10 extra lookup columns, but perhaps there is something we can do to improve performance (caching and such).
If you just need some basic meta-data about a few columns, you can use selectRows specifying a maxRows=0. This will return the full metaData and columnModel sections, but the rows section will be an empty array. The selectRows API allows you to specify a list of columns you want, so you can select just the few columns you need. |
| |
| marki responded: |
2010-03-11 10:48 |
Another question -- do you notice this the first time you are getting this information after an import or does it happen every time?
I'm wondering if we are "materializing" some views of the data when you make this request. |
| |
| Ben Bimber responded: |
2010-03-11 11:29 |
i will try to pay attention to this. i think dave is right that this is likely a more general study performance issue than getQueryDetails() specifically. I think the right thing to do is to follow up properly on the test server, but here are some symptoms I have seen to date on my laptop with the EHR study:
while in the query module schema browser, it is not uncommon for loading the study schema to timeout (meaning expanding the list of tables). Likewise for loading the info page on the participant or study data tables (not data itself).
i have noticed that if i try to load a very big table without any sort of filter, i can cause this slow behavior. for example, trying to load studydata, participant or assignment. i'm not sure how to properly kill one of these queries, but they seem to slow performance after not actually loading.
machine specs:
Running labkey checked out from from 10.1 branch.
postgres 8.4
Dell inspiron Core2duo T7500 2.2ghz
4gb RAM
win7 32-bit |
| |
| jeckels responded: |
2010-03-12 10:17 |
Hi Ben,
I just did a little digging into this. I think that the performance may be significantly degraded if you are running in dev mode, since we end up checking the file system a bunch of times to see if the XML metadata has changed.
If you're running in dev mode, it would be interesting to know if it's faster in production mode. If so, we could potentially be less aggressive and not check the file system if we've already checked in the last few seconds. If not, we can investigate further and track down the source of the slowness.
Thanks,
Josh |
| |
|
|