Date query perfomance

[robrecht]

Hi,

I have attached a screenshot of performance data when loading 1 form.
4 queries are executed 882 times - which is normal - but 1 query is taking 8ms each time.
I see there is date comparison and also an 0R clause, so I suppose one of these is slowing it down.

In code this happens :

Code: Select all

   /** @type JSFoundset<db:/globis_development/stock_salesreservation> */
   var _fs_stock_salesreservation = databaseManager.getFoundSet("globis_development", "stock_salesreservation");
   _fs_stock_salesreservation.clear()
   _fs_stock_salesreservation.find()
   _fs_stock_salesreservation.warehouse_id = _warehouse_id   
   _fs_stock_salesreservation.article_stock_id = _article_stock_id
   _fs_stock_salesreservation.stock_salesreservation_status = _array_status
   _fs_stock_salesreservation.expected_delivery_date = '<=' + globals.svy_utl_dateFind(_date, true)   
   _fs_stock_salesreservation.order_on_hold = 0
   _fs_stock_salesreservation.contract_article_id = _contract_article_id ? _contract_article_id : '^'
   _fs_stock_salesreservation.newRecord()   
   _fs_stock_salesreservation.warehouse_id = _warehouse_id   
   _fs_stock_salesreservation.article_stock_id = _article_stock_id
   _fs_stock_salesreservation.stock_salesreservation_status = _array_status
   _fs_stock_salesreservation.backorder_delivery_date = '<=' + globals.svy_utl_dateFind(_date, true)   
   _fs_stock_salesreservation.order_on_hold = 0
   _fs_stock_salesreservation.contract_article_id = _contract_article_id ? _contract_article_id : '^'
   _fs_stock_salesreservation.search()


What can I do to boost the performance?

I think there are indeces where they are supposed to be

Thanks

[mboegem]

Hi Robrecht,

hard to say what is causing the 8ms.
Although not much on it's own, it is when getting fired 882 times

Did you check your tables for in(correct) indices?

Other thing which might speed up things: try writing a custom query to load your data.
Maybe even show the results using a solutionModel with a custom dataSource.

Based on the info in your post it's still a big guess...

[rgansevles]

Robrecht,

You could try create the query using QueryBuilder, the or-clause would be smaller.

Alternatively you could also run 2 queries.
Suppose the one on contract_article_id hits 90% of the time, the second one could be skipped.

Hope this helps,

Rob