Not a DBA

I don't consider myself an expert in database design or administration, but like many web programmers, my applications often rely on fast and efficient interaction with a database. I am fairly comfortable normalizing a schema and identifying fields to index, but I sometimes struggle creating efficient queries... especially when multiple table joins are needed.

I'm looking for suggestions on how I can improve in this area.

Dynamically Joining Tables

Currently, I'm working with a database containing multiple sets of interconnected objects. Let's assume they are television shows, actors in the shows, and times the shows are on. The goal is to allow users to apply a set of filters to find the shows that interest them. Some simple use scenarios include:
  • Find all shows where ActorA appears.
  • Find all shows on between 10AM and 12 PM.
Some harder scenarios include:
  • Which show or actor appears most frequently.
  • Which shows are on between 10AM and 12 PM, staring ActorA, but not ActorB.
  • Which shows are on between 10AM and 12 PM, or star ActorC.
  • Which shows star ActorA and ActorC, but not ActorB or ActorD.
As you can see from these examples, users can combine filter criteria (actors, times, etc) in a variety of different ways to find the information they need. My team has created a query system that relies on inner queries to search for this information. Performance is not that good. I know that sometimes the order nested queries execute can yield better performance. We're struggling to find the algorithm that will provide the right mixture.

We're also exploring other ideas like
  • utilizing views to try and simplify things. I'm concerned we're just pushing down the complexity to another level.
  • creating stored procedures to perform the filtering logic. Again, will we find performance gains by pushing the complexity down a level?

Discussion

I'm sure this problem is not unique. What techniques have others employed to find performance gains (query optimization, views, stored procedures, something else).

How do you verify that the gains are real? Do you solely look at query plans? If not, how do you keep the query cache from skewing the results?

Comments

Post a Comment

Popular posts from this blog

I Believe...

A recent reorg at my company left me with a new manager - and a different approach to software development than my team had been previously following.  Our new approach is one that I don’t necessarily agree with, but it led me to think about - and reevaluate -  those tenets of software development that I think are important and that I believe lead to increased quality and productivity. This post will consist of fairly high level discussions about how to organize code, teams, and projects - but contain very little actual code. Many of these ideas will not be my own.  They are mostly ideas I’ve learned from others and their willingness to share their experiences with me.  They are, however, ideas that I’ve practiced and hardened as I’ve worked to build my own ideal environment for delivering value through software . Many of these ideas I now take for granted as commonplace (even though they aren’t).  I felt a need to document them anyway - so people can understand what to expect (a
Read more

FRail :include

I really like developing with Rails, but I've been frustrated the last few days by some things Rails should do quite easily, yet doesn't. I've already written here and here about some of my struggles with Rails :include clause. Here's one more... Let's say you have a has-and-belongs-to-many relationship. If you :include this relationship in a find query, Rails will automatically create the join clause including the lookup table! That's cool. Unfortunately, it looks as if only the :include clause provides this piece of functionality. Why isn't the same functionality provided via the :join clause or through a general purpose module? This doesn't make any sense to me! I'm working on a database application where I don't need to display a lot of data, but the data displayed is heavily filtered by it's connections to other information in the database. I need to join a lot of tables to perform the filtering operation and using :include
Read more

Performance Tuning JCAPS - Part 2

It's been close to 2 months since my first Performance Tuning JCAPS post. Since then, we've noticed our servers running out of TCP connections under heavy load. Researching this problem, we learned that the JCAPS' JMS server makes heavy use of TCP in implementing Request/Reply queues . We approached Sun for guidance and they assured us that they've had success implementing high throughput applications using the JMS request reply solution... but how? It appears a multi-step solution is needed. First, running out of TCP connections in a scenario like ours is a JCAPS bug addressed in an ESR (110348) and rolled into JCAPS 5.1.3 Update Release 3 . We installed this update and noticed a marked improvement. Running my simple 75 user simulated test, transactions took 2521 ms to round trip (compared to the previous time of 20128ms). While this is great improvement, an average of 2.5 seconds is still really slow for this simple transaction. We reported our findings to Su
Read more