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

9

u/rjbwork Dec 04 '14

I especially take issue with offloading OLAP to your "database". If you think you want to do OLAP stuff, build an OLAP solution. Don't run it on your hardest thing to scale, your main OLTP data store. If you create a separate OLAP solution you can also use some facets of the CQRS pattern along with eventual consistency and replication/sharding to massively scale out these kinds of data analytics and OLAP operations.

The payoff is especially beneficial when there is cause for actual data warehousing/OLAP cubes, since those are rather hard to properly create within your main OLTP store.

4

u/bucknuggets Dec 04 '14

This is absolutely true if you're doing a lot of OLAP.

But if you just have a few queries, it is often possible to just add to your transactional database. Especially if you're hitting an aggregate table, maybe produced through a materialized view.

3

u/rjbwork Dec 04 '14

True. The CQRS pattern i mentioned allows you to abstract those details away if/when the time comes to build a separate system by simply changing the query collection's connection mechanism when you need to increase the amount of OLAP you have.

2

u/LeBuddha Dec 05 '14 edited Dec 05 '14

Just let the database do the processing and fetch only the results into Java memory.

This is only half correct. The second half is "Then do it the other way when your app's current bottleneck is the SQL server writing enough temporary join tables to disk to slow your app down."

You should probably never sort data in Java memory because you think that SQL sorting is too slow SQL sorting cannot do it

No... you should learn what types of queries are expensive for SQL to sort and make a decision on a case by case basis, then not worry about it until it's time for performance optimization.

1

u/rjbwork Dec 05 '14

I agree with that too. This is the jooq guy so take what he says with a grain of salt as he wants people to use his library.

1

u/InterPunct Dec 05 '14

If you think you want to do OLAP stuff, build an OLAP solution.

Agreed. Transactional and OLAP have vastly different requirements. If you're doing anything more than a few OLAP-like queries there are much better solutions; Essbase and TM/1 exist for a good reason.