I'm using a SQL query that is similar to the following form:
SELECT col1, col2 FROM table1 LEFT OUTER JOIN table2 ON table1.person_uid = table2.person_uid AND table1.period = table2.period
And it's either way too slow or something's deadlocking because it takes at least 4 minutes to return. If I were to change it to this:
SELECT col1, col2 FROM table1 LEFT OUTER JOIN table2 ON table1.person_uid = table2.person_uid WHERE table1.period = table2.period
then it works fine (albeit not returning the right number of columns). Is there any way to speed this up?
UPDATE: It does the same thing if I switch the last two lines of the latter query:
SELECT col1, col2 FROM table1 LEFT OUTER JOIN table2 ON table1.period = table2.period WHERE table1.person_uid = table2.person_uid
UPDATE 2: These are actually views that I'm joining. Unfortunately, they're on a database I don't have control over, so I can't (easily) make any changes to the indexing. I am inclined to agree that this is an indexing issue though. I'll wait a little while before accepting an answer in case there's some magical way to tune this query that I don't know about. Otherwise, I'll accept one of the current answers and try to figure out another way to do what I want to do. Thanks for everybody's help so far.
Bear in mind that statements 2 and 3 are different to the first one.
How? Well, you're doing a left outer join and your WHERE clause isn't taking that into account (like the ON clause does). At a minimum, try:
and see if you get the same performance issue.
What indexes do you have on these tables? Is this relationship defined by a foreign key constraint?
What you probably need is a composite index on both person_uid and period (on both tables).