1

I've a use case where user can type in text and the system searches for matching Invoices. The typed in text can match on multiple fields on both Invoice but also Invoice Lines.

A user has only access to a subset of all invoices based on roles. But this could be a list of thousands of invoices.

The searching is done by a straight-forward like search in combination with wildcards. (Queries are written in hql)

in showing result we want to limit it to 50, as to not select/process thousands of entries depending on what the user searched. However, with the way I join this limit can't be enforced by the database.

We used to have something like (pseudo code)

select * from tbl_invoice
join tbl_useraccess .... :userid //a few joins happen here to limit to invoices             
//where the user has access to
where number like :input
or name like :input
or id in (select id from InvoiceLine where reference like :input)
limit top 50

This has very bad performance as the search is done on each invoice line, instead of just the ones where you have access to, but does always give the correct 50 lines.

I've changed this to

select * from tbl_invoice invoice
join tbl_useraccess .... :userid 
join tbl_invoiceline line on invoice.id = line.invoice_id    
where number like :input
or name like :input
or line.reference like :input
limit top 50

Performance is way better (previous statement would just timeout) but the limit doesn't work because there could multiple lines for one invoice.

We could retrieve all results from the database, map it to the java objects and do the max 50 results in Java but I'm afraid this could blow-up our memory if a user retrieves thousands-millions of records.

So in conclusion, I'm looking for a better way to retrieve a fixed list of results but also be able to search in a linked 1-n entity

Joris
  • 21
  • 6
  • Maybe you can somewhat overcome this by using a *sub-select* but I am not entirely sure how that is done in hql, maybe have a look at this question which provides some additional links: https://stackoverflow.com/questions/8784354/hql-limit-in-subselect-query – Lino Jan 24 '19 at 11:01
  • Hi Lino, thanks for the feedback. We use indeed hibernate setMaxResults() to limit the output. Using a sub query is impossible/difficult(?), as our first effort used a subquery but queried all lines instead of the ones where the user had access to. And the user access is managed on an invoice level. – Joris Jan 24 '19 at 14:28

1 Answers1

0
select * from InvoiceLine line where reference like :input
join tbl_invoice invoice on invoice.id = line.invoice_id  
join tbl_useraccess .... :userid 
where number like :input
or name like :input
or line.reference like :input
limit top 50

this will limit your lines for invoice to 50.

Naya
  • 850
  • 6
  • 19
  • Hi Naya,Thanks for the feedback. My goal is to show 50 invoices, where each of these invoice can have between 1 and 5000 lines. Limiting invoice lines, might only show one invoice – Joris Jan 24 '19 at 14:26