• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1320
  • Last Modified:

Too many JOINS in SQL query

I have a query that has 6 JOINS + 5 JOINS (1 UNION). I'm using it for some baseball analytics. It works fine but it's very slow. I was going to add more JOINS but it is exceeding memory. I read about subqueries and such but I have no idea how to optimize this.

For simplicity I took out all of the columns from the query so you can see the raw code:

SELECT P1.NAME FROM 

(((((PI_PROJ AS P1 RIGHT JOIN MU AS M1 ON P1.Name=M1.Name1) 
LEFT JOIN PF AS PF1 ON PF1.Team=M1.Team2) 
LEFT JOIN PF AS PF2 ON PF2.Team=M1.Team1) 
LEFT JOIN FD_A AS F ON M1.Name1=F.Name) 
LEFT JOIN TEAM AS TM ON (TM.Team=M1.Team2) AND (TM.GameID=M1.GameID)) 
LEFT JOIN BA_PF AS C ON (C.Team=M1.Team1) AND (C.ID=M1.GameID)
WHERE (C.Pos="C" OR C.Pos IS NULL)

UNION SELECT P2.NAME FROM 

(((((PI_PROJ AS P2 RIGHT JOIN MU AS M2 ON P2.Name = M2.Name2) 
LEFT JOIN PF AS PF1 ON PF1.Team = M2.Team2) 
LEFT JOIN FD_A AS F ON M2.Name2=F.Name) 
LEFT JOIN TEAM AS TM ON (TM.Team = M2.Team1) AND (TM.GameID=M2.GameID)) 
LEFT JOIN BA_PF AS C ON C.Team=M2.Team2 AND C.ID=M2.GameID) 
WHERE (C.Pos="C" OR C.Pos IS NULL)

Open in new window


Anyway to optimize/streamline this? I know it's probably hard to tell from the raw code.
0
intoxicated_curveball
Asked:
intoxicated_curveball
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
PortletPaulfreelancerCommented:
ok, a couple of points up front.

1. don't mix left and right joins in a singe query, it gets really messy and very rarely is it really needed. Just flip the first 2 tables around making it a left join. i.e. FROM MU left join ...

2.  when you use the where cause on a left joined table, you must allow for NULLS.
or, you might as well use an inner join

i.e. the left join exists to allow unmatched records to survive the joins, then if unmatched some values will be NULL. If you insist then through a where condition that a value MUST be "some value" it cannot also be NULL

(this is so often overlooked! It can be called an "implicit inner join")

I can't offer  much more right now. Perhaps others will.
0
 
PortletPaulfreelancerCommented:
Oh, and you might try "UNION ALL" this is LESS effort than  union, but I cannot tell if this will cause a change in results.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I was going to add more JOINS but it is exceeding memory.
I can't understand this part. Why is exceeding memory? There's any error? How much memory you have?

What's the big difference between first part of the UNION and the second one?
There's no WHERE clause?
How many records each table has?
The FK's are indexed?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
Would you explain a little about what you are trying to do and what the data columns represent?  There may be a cleaner way in SQL to do what you are trying to do.  If you can put the requirements into words, and describe the data and its roles, we can probably help you re-write this completely.
0
 
intoxicated_curveballAuthor Commented:
Paul Maxwell: I made your suggested changes. Specifically the UNION ALL does seem to make a little difference in performance.

Vitor: The actual error I'm receiving is "System Resource Exceed" from Access. It's not related to my PC's memory as I've tried it on another computer with way more memory (and 64-bit). It's a limitation of Access  I believe.

The difference between the two parts (UNION) is one is selecting all pitchers at home, and the other is all pitchers away. There are various calculation differences between the two. Thinking about it perhaps I will take out the second part and just use IIF statements. WHERE clause not needed after the UNION since I need all records from both parts.

The JOINS are selecting from up to a couple thousand records down to a few dozen.

I haven't implemented any PK or FK's in my tables as I don't know how to do this with player names sometimes being duplicated.
0
 
intoxicated_curveballAuthor Commented:
Scott: Here's what I'm doing:

First selecting all pitchers from current matchups table,
left joining the park factor table from the home team,
left joining the park factor table from the opposing team,
left joining the fantasy points table from the pitchers name,
left joining the team data table from the opposing team,
left joining the batters table from the pitchers team to get the catcher

All of above is used in calculating expected pitcher performance with game variables (points for fantasy).
0
 
PortletPaulfreelancerCommented:
>>"... perhaps I will take out the second part and just use IIF statements."

This will make a big (good) difference. Definitely pursue that line of thought.

and, sorry I notice now that your where clause is fine for a LEFT JOIN (due to the OR ... IS NULL)
not sure what I was thinking when I said that. sorry.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Thinking about it perhaps I will take out the second part and just use IIF statements.
Yes, I wanted to say you that but good that you realized this for yourself. Go for it as I think will be a very good improvement.

I haven't implemented any PK or FK's in my tables as I don't know how to do this with player names sometimes being duplicated.
Post here the tables structure and we will help you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now