Tag Archives: JDBC

postgresql explain

Posted on by
Reply

I had an opportunity to do some tuning on postgresql and was pleasantly surprised at how smoothly it went.

The first thing I did was try to run an “explain” on the query under discussion.  (Explain is a tabular or graphical view of the detailed steps the database uses to execute your query.  By knowing what path it will take and what tables/indexes it will look at, you can tune your query appropriately.) Knowing this works differently in different databases, I looked up what to do.  Here are the steps:

To run explain at the command line:
1) Type “explain” followed by your query.  For example “explain select * from table”.

That’s right – one step!

To run explain graphically:
1) Install pgadmin if you haven’t already
2) Type query into editor
3) Choose query –> explain
This shows the graphical view of the query.  Clicking on the data output tab shws the text view generated by the command line.

Now it may have changed since then, but I needed to create a separate table the last time I ran an explain in Oracle.  This was extra steps that I have to look up each time.  db2 had a good graphical explain built into the tool.

What surprised me here was that I figured out postgresql’s explain much faster than Oracle’s.  Namely because it was so simple!  For the command line version, there is only one step – and it’s not one I am likely to forget.

It’s always nice when software works in such an intutive manner.

Memo: Avoid Nested Queries in MySQL at all costs

Posted on by
17

Some of my readers may be aware that nested subqueries such as “SELECT * FROM widgets WHERE id IN (SELECT …)”, don’t work all that well in MYSQL. While the syntax is usually correct, the performance issues in practice can be horrendous. This article delves deeper into this issue, and why MySQL performs so poorly with nested subqueries, but not so deep as to drive us all crazy.

Nested Queries

Background

The first complex query I learned how to write was a nested subquery, or just nested query for short. At the time I was learning SQL and databases, it was the simple and most obvious of all the complex queries/joins: Find a set of records whose Id is in a list of outputs of another query.

SELECT id,name,price 
FROM widgets 
WHERE id IN (SELECT DISTINCT widgetId FROM widgetOrders)

In the example above, we first query the widgetOrders table for all unique widgets that have been sold based on widgetId (the DISTINCT doesn’t change the output of the query, but can help performance). After we have such a list, we select the id, name, and price of those widgets using data from the widget table.

First off, why do people like nested queries? As I said, they are pretty easy to understand, ESPECIALLY for non-programmers. But what are the alternatives to nested queries? Joins! Non-programmers (and even some programmers) find joins to be mystifying and scary things. Words like INNER JOIN, RIGHT OUTER JOIN, or even the shortcut symbols *= scare a lot of people. For example, the previous query can be rewritten as an INNER JOIN as follows:

SELECT DISTINCT w.id,w.name,w.price 
FROM widgets w 
INNER JOIN widgetOrders o ON w.id=o.widgetId

So why is this scarier? First, while aliases like ‘w’ and ‘o’ for table names were previously optional, they become almost required with complex joins, since we’re essentially mixing a two level query into a single level. Also, we have to add new syntax such as INNER JOIN … ON. There’s a lot more going on, and a lot more for beginners to pick up and/or be scared off by.

Why nested joins are bad in theory

The first big question of this article revolves around how query optimizers work. You can write a query a thousand different ways that would all output the same information and might seem equivalent to you, but query optimizers are just not that smart. The search space they have to cover to effectively optimize a query is massive, longer than could ever be searched in a reasonable amount of time. Therefore, query optimizes are often collections of greedy algorithims. Sure, they will do intelligent things when they can figure them out in time, but often they just look for the ‘quick path out’ using some simple heuristics. If a query optimizer thinks a particular plan may be the fastest, it won’t necessarily spend time verifying it; it will just act. Because of this, it is very easy to trip up or hinder a query optimizer.

This brings us to nested queries. Even the best query optimizers in the best database software available have trouble with nested queries. This is because they often cannot optimize them in any reasonable manner. As we saw in the example, I took two separate queries and merged them into one. Most query optimizers are not smart enough to do this since finding such a conversion would take too long, or in computing terms would require too large a search space and near-infinite time. In fact, many query optimizers will flat out refuse to optimize nested queries if it sees them. Therefore, a general rule of thumb is to avoid nested queries as much as possible since you are essentially blocking the query optimizer from touching that part of the query. You should stick with more traditional joins as much as possible since this encourages the query optimizer to find better query paths.

Why nested joins are really bad in MySQL

While nested queries may have been the first type of complex query I worked with, I never had serious problems with them and never spent hours reworking them to non-nested queries, until I started working in MySQL. Many nested queries you might easily write are capable of completely grinding your MySQL database to a halt under certain data conditions. MySQL has posted a list of excuses and tips (to fix your queries instead of their code) and there’s numerous forums posts, blogs, bug reports, and articles discussing the issue, but I’ll streamline it for you: MySQL does terrible things when handling nested subqueries; therefore, if you are using MySQL they should be avoided at all costs.

Note: This does not mean you should avoid the IN or NOT IN syntax, for example “WHERE id IN (1,2,3)” is just fine. The problems is when “1,2,3” is replaced with a subquery such as “SELECT …”.

But Scott, I need a nested query!

If you absolutely need a nested query, you can always perform two distinct queries in your application as such:

Set X = CallDatabase("SELECT DISTINCT widgetId FROM widgetOrders");
CallDatabase("SELECT id,name,price FROM widgets WHERE id IN ("+X+")");

As strange as it sounds to recommend two database calls over one, there are many real cases in MySQL where this will perform better than nested queries.

The Future

The problem with nested queries is that in many circumstances they will perform just fine, but change the data slightly and they can seriously harm database performance in MySQL. For example, strange things can happen if the subquery returns no records so that you end up with “WHERE id IN ()”. Many of the issues with subqueries have been logged as bug on MySQL’s support site, so it’s possible in future versions they will be safer to use. For now though, avoid them as long as you program with MySQL, lest you want to create headaches for yourself down the road.

5 Tips to be a Good JDBC Programmer

Posted on by
12

Have you written Java code for your database connections in JDBC that ends up being thrown away? Would using a new database software easily kill your product and set you back months at a time to port? Well, if so, keep reading as the goal of this article is to make you a better JDBC programmer.

Database

    Tip #1: ALWAYS close connections

I wrote an entry about closing database connections back in July so I won’t belabor the point. Suffice it to say, one of the most common mistakes JDBC programmers make is failing to close database resources like result sets, statements, and connections. Unfortunately, because of the nature of the problem a developer will often never see the error until it is discovered in a production environment since most developers test with one process at a time. In short, every time you open a connection you should close it sometime later and that close should be nearly bullet proof, as so:

Connection con;
try {
   con = ...
} finally {
   try { if(con!=null) {con.close();}}
   catch (Exception e1) {}
}

As mentioned in the post about the subject, you can replace the finally code with a call to a method such as closeConnection() which does the same thing.

    Tip #2: Always use PreparedStatements over regular Statements

As you may be aware, PreparedStatements come with many advantages over regular Statements:

  • Sanitizes your database inputs: Great Example
  • Organizes your statements into set of input/output commands
  • Performance boost: The pre-compiled statement can be reused multiple times

The mistake a lot of programmers make is when they say “Well, I have no inputs to the query, so I’m not going to use PreparedStatements”. First off, they may have inputs down the road they needed to ‘tack on’ to the query, and it will save a lot of time rewriting the code if the query is already structured as a PreparedStatement. Second, perhaps there are some hard-coded input values that you can’t imagine changing, but could change down the road. Better to use a PreparedStatement and a static object, than to hard-code it, as shown in this example:

Statement statement = con.createStatement();
statement.executeQuery("SELECT name FROM widgets WHERE type = 'WidgetB'");

versus:

final String widgetType = "WidgetB";
Statement pStatement = con.prepareStatement("SELECT name FROM widgets WHERE type = ?");
pStatement.setString(1,widgetType);
pStatement.executeQuery();

Sure, the first example is shorter, but the second gives you the freedom to easily change the Widget Type down the road as well as supporting additional inputs since the setup work is all ready done.

    Tip #3: Be Prepared To Change Databases

It’s not a myth; database platforms do change. You may be working on a project using Oracle and you get a request from your manager for an estimate to port the code to MySQL. It can and does happen, the question is are you going to respond with “a couple of days/weeks” or “12-24 months”. Your answer should shed some light on how good your code really is.

    Tip #4: Never Reference Database-Specific Libraries

If you ever find yourself writing a line of code such as this:

OracleCallableStatement cst = (OracleCallableStatement)conn.prepareCall ...

Stop! This is an example where a regular Callable Statement is likely called for, but you’re using a database-specific line in your code. The whole purpose of JDBC driver pattern is that you can drag and drop new JDBC driver libraries for MySQL, Oracle, etc into your application and the software will still compile and run just fine. Lines of code like the one above require you have the JDBC library available at compile-time and guarantee you’ll have to rewrite code to port to another language. In other words, writing a line of code like this is a developer admitting to themselves “I will definitely need to rewrite this line of code if I want to port to another database”.

    Tip #5: Never use Database Stored Procedures*

Never, ever, write a Java application that relies, in large part, on stored procedures. Not only can they never be ported to a different database, 9 times out of 10, you can’t even recall exactly what they did when you wrote them. They fall into the same category of Perl code, which is WORN (write once, read never), since most of the time reading them is harder than rewriting them, mostly because TSQL/PSQL are not very pretty languages to work with.

* There some cases where stored procedures are allowed, such as reporting statistics or materialized views, but these are few and far between. The bulk of your main application logic, such as adding new users, performing basic transactions, and editing data should not rely on stored procedures in any way. Reporting is allowed here because often times performing metrics on millions of records in JDBC would require too many round trips over the network. In other words, reporting requires the kind of direct network access stored procedures guarantee since they run on the database itself. But if you are stuck using them, you should insulate them so they don’t interact with any of your java code directly, such as an independent nightly build of statistics.

    Bonus Tip #6: Never put JDBC code inside JSPs

I started with 5 tips but our reader’s comments reminded me of a 6th tip: Never put JDBC code of any kind inside a JSP! The purpose of a 3-tiered architecture is to get good separation of layers, and putting JDBC code inside the presentation tier skips the middle layer all together. Some developers reduce the impact of this mistake by putting their connection code inside a Java file and only passing the result set to a JSP. This is still really bad! JSPs should have no JDBC code whatsoever since most of the time queries can be reused by multiple modules and JSPs don’t exactly lend themselves to resusability.