1

I'm currently a junior developer working on a web application with a Java/DB2 backend and I have some SQL queries that run quite slowly. The database right now is not optimized so there's definitely room for improvement. Only problem is that I have no experience with this and no one can help me.

SomeTableName
MyPkey
ColOne
ColTwo
ColThree
ColFour
ColFive

I was trying to figure out how to optimize the database for queries like these:

SELECT * FROM SomeTableName WHERE ColOne = 'some value'
SELECT * FROM SomeTableName WHERE ColOne = 'some value' AND ColTwo = 'another'
SELECT * FROM SomeTableName WHERE ColFive = 11 AND ColThree = 'hello world'
SELECT * FROM SomeTableName WHERE ColOne = 'hi' AND ColTwo = 'val1' AND ColThree = 'val2' AND ColFour = 'val3' AND ColFive = 'val4'

What I'm trying to portray is, the SELECT statements have WHERE conditions that have different column combinations and values and I'm not sure how to optimize queries like this.

Any advice?

EDIT: I'm not sure if DB2 adds its own optimizations but for sure there are NO indices setup on any of the columns.

James Maxwell
  • 31
  • 1
  • 4
  • The first place you should look is the table definitions, although I don't know much about Java or DB2 so I can't help you with specifics. In most sql situations queries like this can be vastly improved by making sure there are appropriate indexes. – Henry May 01 '11 at 22:50
  • The database doesn't have any indices. I thought of creating one before but not sure how to set it up (ie. Should I do one index per column? Or should I have multiple columns in one index?) – James Maxwell May 01 '11 at 22:52
  • You're pretty much doing the simplest thing you can do with a DB. If they're running slow you should add indexes to the table. Without them, you're doing a full table scan every time. – Brian Roach May 01 '11 at 22:57

3 Answers3

0

You should definitely play with indexes on your database. They make insertion and update more expensive, but your queries will run much faster, so they're probably a big win overall. Indexes are most effective for columns that have large numbers of values (ie, not so much for a gender column). They're more effective for columns you reference a lot in your where conditions. As you try out indexes, be sure to see if your database's query optimizer is leveraging them, using the SQL EXPLAIN verb or a tool like db2expln.

Community
  • 1
  • 1
Jim Ferrans
  • 30,582
  • 12
  • 56
  • 83
0

From your example, it looks like you'll already gain some performance just by indexing colOne. Basically, use the columns in the same order as the index you want to use.

clyc
  • 2,420
  • 14
  • 15
0

You would get a decent performance gain by creating an index on ( colOne, colTwo, colThree, colFour, colFive ). Queries 1, 2 and 4 will all use it.

judda
  • 3,977
  • 1
  • 25
  • 27