sqls | SQL language server written in Go | SQL Database library

You are, in this case, running 1 query and instantiating a collection with 10k objects looping that entire collection 10k times to find a single entity.

Vs.

Sending 10k queries to a hyper-optimised, indexed database solution built for that exact purpose and getting back a single entity.

On top of that, your queries aren't looking at the same thing, so naturally, it will be even slower.

Simply asking a database solution to give entries WHERE X = Y is always going to be faster than asking the same in the format of WHERE X > 0 AND X < 10000

The slow down comes from instantiating a collection of 10000 entities and looping them repeatedly 10000 times while trying to find where test2 = id.

Whereas with SQL, you can find that much faster than you can by looping it in PHP.

In short, you could rephrase your question:

is it faster to query a database 10k times or to loop a collection of 10k entities 10k times in plain PHP while comparing the value of each record to an ID?

To which SQL becomes the more obvious answer.

Given your requirements, the only way I can see to do this would be to bring all of the results for each user together so that they are written by the same instance of the sink.

Perhaps it would work to rewrite this as one large join (or union) on the user-id that you sort by timestamp. Or you might convert the results of the SQL queries into datastreams that you key by the user-id, and then implement some buffering and sorting in your custom sink.

Found in disucssions solution to add inserts to PL/SQL block:

ShadelessFox It's not possible from a DBeaver perspective, but you can use PL/SQL blocks

That's precisely what the SQL Server FOR JSON clause does, which jOOQ supports and which it can emulate for you on other dialects as well:

This is possible through lambda if you wanna use the bash script. But if you wanna just execute sqls, wrap those sqls in a .sql script and use Redshift query scheduler to schedule these via either manual schedule run or cron job schedule run

For a start I suggest to take the trouble and enable SQL logging in Hibernate while developing - see here. Knowing the exact statements Hibernate creates for your JPA queries is invaluable, e.g. you have a chance to spot N+1 query problems, excessive joins etc.

Having said that, in your case the statements should look like as follows:

• cb.equal(root.get(MyEntity_.myOtherEntity), myOtherEntity) → SELECT ... FROM MyEntity WHERE MyEntity.myOtherEntity_id = ?. In cases like this, Hibernate usually knows to optimize and avoid the unnecessary join.

• cb.equal(root.get(MyEntity_.myOtherEntity).get(MyOtherEntity_.id), myOtherEntity.getId()) → Should be like above; again Hibernate should know that the .get(MyOtherEntity_.id) is already in the table and avoid the unnecessay join.

  I have seen Hibernate working the way I describe for the cases above. Definitely enable SQL logging to verify, there may be details for your own use case that make it behave in a different way!

• cb.equal(root.get(MyEntity_.myOtherEntity).get(MyOtherEntity_.name), myOtherEntity.getName()) → Will definitely create a join because it cannot find myOtherEntity.name in the MyEntity table: SELECT ... FROM MyEntity e JOIN MyOtherEntity oe ON ... WHERE oe.name = ?

The first SQL that I posted shouldn't have been run:

First : If you need a result set for multiple classes (an abstract result set) your load times would benefit from having the sub classes parent mapped (Subclasses written in one - parent - table)

To get the generated SQL from a PSQuery:

Generally, the like operator has limited functionality, certainly less than regexp. However, what you need can be done by

1. Appending a space to the beginning and end of your search string to ensure it's a different word
2. Doing the same to the target string, so that it will also be matched in the edges:

where ' '+folder+' ' like '% old %'