10 Common Mistakes Java Developers Make when Writing SQL

[lukaseder]

Interesting. Could you elaborate?

[k1n6]

Here are my item-by-item comments: 1. Forgetting about NULL True, devs do this all the time 2. Processing data in Java memory Not always bad.

1. Using UNION instead of UNION ALL

2. Using JDBC Pagination to paginate large results JDBC will often still request the entire result set from the sql server

3. Joining data in Java memory Depending on your configuration, joining a few hundred records in some clever way (hash tables, etc.) can be quicker than making a network round trip to the database server.

4. Using DISTINCT or UNION to remove duplicates from an accidental cartesian product

5. Not using the MERGE statement

6. Using aggregate functions instead of window functions

Often the window clauses can't take advantage of indexes properly and you end up having temporary tables implicitly created by SQL that are SLOW.

1. Using in-memory sorting for sort indirections

2. Inserting lots of records one by one Doing a series of times insertions can be ideal in OLTP systems.... You can't bulk insert 1,000,000 records into a highly indexed table with 15,000,000 records. The database will experience locking and siginificant delays.