Technical Support Forum Index
Technical Support Forum
Access ChemAxon scientists and developers here. For registration and login issues contact website support.
Sorting in a child entity
To watch this topic for replies  Register (enables digests) or give email address:
Reply to topic
Display posts from previous:   
    View previous topic :: View next topic    
Author Message
pellianna

Joined: 16 Aug 2011
Posts: 55

View user's profile

Back to top
Link to postPosted: Wed Feb 29, 2012 11:54 amPost subject: Sorting in a child entity Reply with quote

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

fzimandl
IJC personnel
Joined: 23 Jun 2011
Posts: 383

View user's profile

Back to top
Link to postPosted: Thu Mar 01, 2012 12:16 pmPost subject: Reply with quote

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

pellianna

Joined: 16 Aug 2011
Posts: 55

View user's profile

Back to top
Link to postPosted: Fri Mar 02, 2012 10:22 amPost subject: Reply with quote

 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

phamernik
IJC personnel
Joined: 25 Jan 2006
Posts: 211

View user's profile

Back to top
Link to postPosted: Tue Mar 06, 2012 2:04 amPost subject: Reply with quote

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

Reply to topic
Page 1 of 1


To watch this topic for replies   Register (enables digests) or give email address  
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum