10 Common Mistakes (Java) Developers Make when Writing SQL

1 . 10 Common Mistakes (Java) Developers Make when Writing SQL Charly Batista Percona charly.batista@percona.com 1 © 2019 Percona 

2 .Charly Batista ● Senior Support Engineer at Percona ● MySQL and PostgreSQL expert ● Working with development and databases for over 20 years ● Speaker at Percona Live and meetups about Database and Development 2 © 2019 Percona 

3 .Agenda ▪ Do not be scared of writing SQL ▪ The infamous SELECT * ▪ Do you really know how NULL works? ▪ Is this column integer or varchar? ▪ The order of the columns doesn't matter, does it? ▪ We have a lot of memory, let's use it!! ▪ We need to paginate the result ▪ Do not underestimate the power of character encoding ▪ Connection pool ▪ Let's talk about batch... 3 © 2019 Percona 

4 .Bonus... ▪ Database Transactions ▪ The power of the Prepared Statements ▪ The index is our friend! 4 © 2019 Percona 

5 . Do not be scared of writing SQL SQL isn't rocket science ;-) 5 © 2019 Percona 

6 .SQL is our friend • SQL is simple and easy to use • It can give a very good readability • Easier to tune and improve performance • Have you ever tried to map a complex join? • How about batch processing? 6 © 2019 Percona 

7 .Tweak our hundreds of annotations, or... 7 © 2019 Percona 

8 .Do you really need all columns? The infamous SELECT * • It can prevent optimizations • Index only scans / covered index • It can bloat the DB memory footprint • Can also bloat the app server memory footprint with massive ResultSet’s • Let’s not forget about our wires… Network will also suffer! 8 © 2019 Percona 

9 .Do you really know how NULL works? You may be surprised!! • Even though JDBC maps SQL NULL to Java null they are not the same • NULL is also called UNKNOWN • SQL “NULL = NULL” is false (not the same as null == null in Java) • Arithmetic operations with NULL may not results in what you expect! 9 © 2019 Percona 

10 .Is this column integer or varchar? Data type mismatch in predicates may ruin your day! • Be careful with the Statements and the Prepared Statements • Also when joining tables to do not use columns with different data types • It may cause data loss (truncate, implicit conversion, etc) • It can cause the database to avoid an index 10 © 2019 Percona 

11 .The order of the columns doesn't matter, does it? Not all indexes are the same, nor are the databases! • The order of the columns is very important for composed indexes • The columns are evaluated from the left most in the index creation order • The order you declare the columns in the where clause doesn’t impact • Cardinality and operators are very important 11 © 2019 Percona 

12 . Let’s talk about Java!! It’s time!!! 12 © 2019 Percona 

13 .We have a lot of memory, let's use it!! Please, do not process data in Java memory when it can be done by the DB • NEVER load two tables from separate queries into maps and join them in Java memory • Do not order, aggregate, execute nasty maths if it can be done by the DB • It is, for most of the cases, easier to write correctly in SQL than in Java • The database will probably be faster than your algorithm • Remember those wire plugged to your server? They will thank you! 13 © 2019 Percona 

14 .We need to paginate the result More often than you think! • The database is your friend and has nice features: • LIMIT .. OFFSET, TOP .. START AT, OFFSET .. FETCH • Remember the memory? How about the wires? They will all love you! • In order to be able to sort and paginate, we have to fetch the complete result set into app memory • Ordering and paginating on DB is way faster than pagination in memory • we can avoid early row lookup and improve the performance even more 14 © 2019 Percona 

15 .Do not underestimate the power of character encoding My database speaks many languages • Make sure that the application and DB are using same charset • Encoding mismatch can: • Completely mess your app “view” • Prevent you from using an specific language or symbols • Cause data loss • It can also cause odd errors that are hard to debug: • ORA-01461: can bind a LONG value only for insert into a LONG column 15 © 2019 Percona 

16 .Connection pool It may be obvious but many people do not use one! • Standard. It is present in the JDBC specification (3.0) • Can greatly increase the performance of the app, while reducing overall resource usage • Reduced connection creation time • Controlled resource usage 16 © 2019 Percona 

17 .Let's talk about batch... The more the better • It’s way faster than single inserts • JDBC knows batching! Create a batch INSERT with a single SQL statement and multiple bind value sets • Try to load the data during off peak time • If the above is not possible try to commit smaller batches to: • Keep the UNDO log small • Avoid locks and race condition 17 © 2019 Percona 

18 . Bonus Do we have time? 18 © 2019 Percona 

19 .Bonus... ▪ Database Transactions ▪ The power of the Prepared Statements ▪ Use the index Luke! 19 © 2019 Percona 

20 . USE CODE UNIVERSITY FOR A 20% DISCOUNT 20 © 2019 Percona 

21 .Join in: Percona Community ● Write for our community blog percona.com/community-blog ● Join in with our community forums percona.com/forums ● Contribute to our open source projects 21 © 2019 Percona 

22 .We are hiring! ● We are a remote first company ● Current EMEA roles: ○ Software Engineer with C/C++ ○ MySQL DBA ○ Build/Release Engineer ○ Technical Account Manager ● We offer $1000 for successful referrals - to anyone! ● See percona.com/careers for more info or talk to us today! 22 © 2019 Percona 

23 .Thank you! Questions? 23 © 2019 Percona 

24 .Champions of Unbiased DATABASE PERFORMANCE Database Performance Matters OpenMATTERS Source Database Solutions