Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(This is cross-posted from stackoverflow; I initially put it in the wrong place.)

So I have a database that's working a bit slower than I would expect and I'd like to describe what I'm doing so hopefully I can verify I'm not missing some performance tweak. Some details first:

  • I'm using java and the database is H2 embedded (although I'm seeing similar results regardless of database).

  • I've profiled / timed it and the problems are definitely in the database queries, specifically the executeQuery() call.

  • For this example the database table has 60K rows but my query involves a subset of 25K rows yet takes a full 1/2 second to return.

Here's how I've set up the database table:

CREATE TABLE rows (
  id @AUTOTYPE@ PRIMARY KEY,

  x VARCHAR(255) NOT NULL,
  y VARCHAR(255) NOT NULL,
  z @TIMETYPE@ NOT NULL
)

CREATE INDEX rows_index ON rows(x, y, z);

(Note that the parameterization is for auto increment and timestamp, respectively)

And my query looks like this:

SELECT * FROM rows WHERE x = ? AND y = ? ORDER BY z DESC

Now, I only care about the first row, but it needs to be the newest row, so in java it looks like this:

PreparedStatement stmt = conn.prepareStatement(query);
stmt.setString(1, "foo");
stmt.setString(2, "bar");
stmt.setMaxRows(1);

Notice the last line has the setMaxRows() set to 1. To reiterate, the entire 'rows' table has about 60K rows and the query would return 25K rows if I wasn't setting it to return only 1.

So am I doing everything I can? Even though 1/2s (0.5s) is certainly not a long time it doesn't seem like 60K records should take that long, especially when it should be indexed. And I know time is relative, so this is just a modern workstation, not running on some sort of load-balanced server.

Thoughts? Thanks for taking a look.

share|improve this question
1  
Even if you fetch only 1 row, the database still has to do some extra work, because of the order by and the default ascending index order. While I am not familiar with H2, I think creating the index as (x, y, z desc) should help. –  Balazs Papp Mar 20 at 23:42
1  
I googled and found that there is an EXPLAIN statement you can use to get the optimizer plan. compare the following queries SELECT * FROM rows WHERE x = ? AND y = ? ORDER BY z DESC and SELECT * FROM rows WHERE x = ? AND y = ? ORDER BY z The latter should use the index and be faster if the assumptions of @Balazs Papp are right. compare to SELECT * FROM rows WHERE x = ? AND y = ? without any sorting. –  miracle173 Mar 21 at 0:32
    
But I found stackoverflow.com/questions/10175605/… and I think it is possible that stmt.setMaxRows(1); does not limit the data retrieved by the database to one row.There is a LIMIT and a TOP clause to restrict the number of rows sent from the darabase. Check if this makes any difference. Maybe you can show us the explain output and the profiling results. –  miracle173 Mar 21 at 0:33
    
@miracle173: I did indeed try to use LIMIT and it didn't make a difference. I found the H2 docs and it said you can use them interchangeably (i.e., LIMIIT or setMaxRows()). Thanks for the information about EXPLAIN, I had never heard of it, and it is something I will look into. Thanks! –  rjcarr Mar 21 at 0:35
    
@BalazsPapp: I haven't tried an index like that, and didn't know it was possible, but I will give it a shot as I've now seen it in other places. Thanks! –  rjcarr Mar 21 at 0:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.