0

I have a query in the following form

Query query = getEm().createNativeQuery("SELECT i.claimid, i.attachmentfilename, i.invoiceid, i.invoicedate, i.invoicenumber, "
                + "i.invoicetypeid, i.dealerinvoicenumber, i.bdxid, i.capturedate, i.paid, i.paymentdate "
                + "FROM invoice i WHERE i.claimid = "+claimId
                        + " AND i.invoicetypeid = 2 order by i.invoicedate DESC LIMIT 1", Invoice.class);
        logger.info(claimId+"=starting..");
        List<Invoice> invoices = query.getResultList();
        logger.info(claimId+"=ending..");

The issue is once it prints "starting..", it's hanging for as long as 10min, only to pull 1 or 2 results. How can 1 or 2 records take so long?

There is a one-to-one relationship between Invoice and Claim, but it's only defined from Invoice.

Can anyone explain why that line takes so long?

Brad
  • 15,186
  • 11
  • 60
  • 74
Bongo
  • 333
  • 3
  • 12
  • What all index are associated with invoice table? How many rows are there in this table? – SMA Mar 12 '17 at 06:42
  • what happens when you execute the query through SQL management studio ? does it still take time ? – Stavm Mar 12 '17 at 06:42
  • what database are you using ? – niceman Mar 12 '17 at 07:02
  • @Stavm - when I execute the query in psql or pg_admin, it runs very fast, 22milliseconds – Bongo Mar 12 '17 at 08:14
  • @niceman - am using postgresql DB – Bongo Mar 12 '17 at 08:14
  • @ngonidzashe if it works fast when you execute your query on the server, and works slow from your end point, this probably means you have a network issue. to make sure can you start a new project, that has no logic in it, but the query, see if it goes fast or not. – Stavm Mar 12 '17 at 08:25
  • @SMA - there is only 1 index invoice_pkey - primary key – Bongo Mar 12 '17 at 08:25
  • @Stavn - but the database is on localhost, there is not network involment. – Bongo Mar 12 '17 at 08:26
  • that's good, keep isolating, I would still start with a blank project, that only executes the query, and start comparing see where it fails. – Stavm Mar 12 '17 at 08:28
  • The number of claims is 227, a claim might have 0,1 or 2 invoices at most, the problem arises when we have more than 0 claims on invoice, thats the only time when query.getResultList() hangs for too long. Which doesn't make sense. – Bongo Mar 12 '17 at 08:31
  • yes but you said the query works very fast when run directly, i could be wrong but i'm not sure it's the data or its layout that should concern you. – Stavm Mar 12 '17 at 08:33
  • how about the database driver (jar) is it latest? – Yazan Mar 12 '17 at 12:09

0 Answers0