Sorting in a child entity

Hi,

I have a project where I am using IJC to connect to an Oracle database. I have two materialized views. One Contains structure and Structhre name, the other one biological data.

I have created a 1:n relationship between the two and a form where I am using a table widget to display the biological data.

I run a query by structure name that retrieves around 3000 rows of biological data. I am finding that sorting on the child entity ( am sorting ascending on the IC50 value)  is taking forever (> 10 minutes). During this time IJC is blocked. The value column is indexed.

Is there anything I need to be aware of that I might have forgotten that can help speed this up? The field is indexed.

Also, where can I look up the SQL that IJC is generating?

 

Many thanks

Anna

Hi Anna,

something very strange is going on here. For enabling SQL login go to Tools->Options->Miscellaneous->Other Settings and enable SQL logging by ticking Write all SQL statements to logs. Please restart IJC, proceed with the ploblematic procedure and send me the log file.

Can you try to log in directly to the DB and run something like this: select  * from TABLE_NAME order by IC50_COLUMN_NAME; If this is fast we must look into the SQLs statements generated by IJC.

Thanks in advance for the log.

Filip

 Hi Filip,

The statement select * from TABLE_NAME order by IC50_COLUMN_NAME takes 50 seconds when done via SQL developer.

FYI, the table has > 13 Million rows.

In IJC I first looked for a particular substance. this retrieved around 2000 rows in the child entity. I then tried to sort by assay_name. IJC was blocked for 20 minutes.

I will send you the log file per email.

 

Kind regards

Anna

Hi Anna,

thank you for the log file and more details, it was useful.

We are still working on investigatation of this performance issue. Just a few ideas for now:

It's possible that it's somehow related to 2000 detail rows, because these are split into more IN (...) parts in executed SQL. If possible, could you try to select a structure which has for example <100 detail rows  and test sorting on this?

Maybe you can try to switch off "Initial data retrieve" in schema editor under schema configuration. Except loading of all data in the beginning it also prevents some loading of total row count. This can be slow for 13mil rows tables. Also there is a difference between sorting using toolbar button and popup menu (we are lookin at this as well). Popup menu is freezing IJC, but toolbar button is starting sorting on background (progress bar is moving).

We will let you know soon about the progress.

Best regards,

Petr