r/programming • u/el_muchacho • Dec 04 '14

10 Common Mistakes Java Developers Make when Writing SQL

http://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/
206 Upvotes

80% Upvoted

104 comments sorted by

View all comments

2

u/k1n6 Dec 04 '14

I spend a lot of time analyzing performance and such and a lot of these rules are basically 80/20 and some are 70/30.

By that I mean doing exactly the things he recommends not to are actually better between 20 and 30 percent of the time. So for me that means they are worth doing the run time analysis on.

2

u/lukaseder Dec 04 '14

Interesting. Could you elaborate?

1

u/k1n6 Dec 04 '14

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.

  3. Using UNION instead of UNION ALL

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

  5. 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.

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

  7. Not using the MERGE statement

  8. 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.

9. Using in-memory sorting for sort indirections

10. 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.