Jinq 1.8.35 -- sortedBy extremely slow

[Laszlo Szijarto]

I am just starting with Jinq, and it's amazing.  I am having difficulty with .sortedBy (and sortedDesceindingBy).  Every time I try to add the sorting (even on integer primary key or indexed column), it is incredibly slow and times out.  Do you have any tips on how to use this?  I have tried putting it in different orders, etc.

[Jinq]

You can use the getDebugQueryString() command on the stream to see what query Jinq is sending to the JPQL/Hibernate layer. That way, you can narrow down whether it's a problem with Jinq's query generation or with the database query execution.

[Laszlo Szijarto]

Thank you, sir.  I found out that the reason was that after a certain time, there were too many open connections because I was failing to call .close() on the streams.  I did not know that I had to do this.  If there's an Exception from a query, does it have to be enclosed in  try-catch block in order to close the EntityManager connection or does it close automatically after an Exception?

[Laszlo Szijarto]

It was just a coincidence that it happened after I added the sortedDescendingBy.

[Jinq]

I'm not sure I understand. You don't close Jinq's streams. They automatically close when you call toList(). 

Jinq only manages queries though. Managing transaction boundaries, the entity manager, database connections, and the JPA lifecycle is outside of Jinq. You have to manage that yourself. Those things differ from application to application, and Jinq doesn't deal with it at all. Many programmers redelegate responsibility for managing those things to the JavaEE / Jakarta application server, but you can also do it yourself if you need to.

[Laszlo Szijarto]

Thank you for the responses, sir.  I had one query where I ran it over and over again, but then after about 7-8 times, it was suddenly hanging.  Perhaps I am doing something wrong with the queries.

I have this query here (touched up to remove sensitive data) that retrieves results in about 129ms

select * from listing where customer_name = 'CUSTOMER' and list_key like 'abc%' and
status in ("A","B","C","D")

So, with JINQ I attempt to write this as follows, but it hangs indefinitely ...

JinqJPAStreamProvider jpa = new JinqJPAStreamProvider(emf);
JPAJinqStream<Listing> ls = jpa.streamAll(emf.createEntityManager(), Listing.class);
String statuses = "A,B,C,D"
List<String> statusesList = Arrays.asList(statuses.get().split(","));
List<Listing> listingSearchResults = ls.where(l -> l.getCustomerName().equals("CUSTOMER") &&
                                  l.getListKey().startsWith("abc") &&
                                  JPQL.isInList(l.getStatus(), statusesList)).toList();

There were other queries that ran fine most of the time, but then  sometimes did not.  That seemed to improve when I was closing the JinqStream manually, so I figured it was some open or hung connection.

Probably the most amazing benefit of JINQ over JPA would be in conditionally adding conditions.  So for the above, I would have

ls = ls.where(l -> l.getCustomerName().equals("CUSTOMER"));

and then based on conditional logic

if (statuses != null) {

List<String> statusesList = Arrays.asList(statuses.get().split(","));

ls = ls.where(l -> l.JPQL.isInList(l.getStatus(), statusesList)

[Jinq]

If you create your own EntityManager, then, yes, you are responsible for closing it yourself.

Most Java programmers don't create their own EntityManagers, so they don't have to close them. (They use an application framework that will create and close EntityManagers automagically.)

[Laszlo Szijarto]

Thank you.  Does closing the Stream also close the Entity Manager or is that a separate operation?  Finally, I had posted a bit of code that was deleted, so I can try again very generically.

Does the expression  

myStream.where(e -> e.getField1().equals("value1") && e.getField2().startsWith("value2") &&  JPQL.isInList(e.getField3(), String[])   

properly simulate the SQL ...

... WHERE (field1='value1' and field2 like 'value2%' and field3 in ('text1','text2','text3','text4')

When I run the SQL statement above, I get a response in about 150ms, but when I run the JINQ syntax above, the response takes a very long time.  Perhaps I am doing something wrong?

[Jinq]

Jinq only does queries. It will never close the EntityManager because you will often need to reuse the EntityManager multiple times within your transaction. You can’t reuse the EntityManager if Jinq closed it. Also, it’s unclear if your entities returned by Jinq would still be valid if the EntityManager were closed.

I don’t know what SQL is generated by Jinq. Queries go through multiple layers. Jinq only generates JPQL queries which are then further translated in SQL by Hibernate or whatever ORM layer that you’re using. You’ll need to turn at SQL logging/debugging at the ORM layer or database layer to see what is actually being sent to the database.