We help IT Professionals succeed at work.
Get Started

SQL Cartesian Product query with many permutations takes too long

371 Views
Last Modified: 2014-08-21
This is sort of in relation to my previous question which was answered: https://www.experts-exchange.com/Database/MS_Access/Q_28429630.html

Note that it's only taking the data from one table (not 9 as I said before).

I have succeeded in creating a MS ACCESS SQL query that will generate all possible teams (permutations) from 8 positions by name ( i.e. catcher, first baseman, second baseman, etc.). Not including the pitcher position. There's about 30 players at each position.

The query is pretty simple actually and it works:

SELECT T2.C, T3.[1B], T4.[2B], T5.[3B], T6.[SS], T7.[OF] AS OF1, T8.[OF] AS OF2, T9.[OF] AS OF3, 
FROM (SELECT Name AS C FROM MASTER WHERE POS='C')  AS T2, (SELECT Name AS 1B FROM MASTER WHERE POS='1B')  AS T3, (SELECT Name AS 2B FROM MASTER WHERE POS='2B')  AS T4, (SELECT Name AS 3B FROM MASTER WHERE POS='3B')  AS T5, (SELECT Name AS SS FROM MASTER WHERE POS='SS')  AS T6, (SELECT Name AS [OF] FROM MASTER WHERE POS='LF')  AS T7, (SELECT Name AS [OF] FROM MASTER WHERE POS='CF')  AS T8, (SELECT Name AS [OF] FROM MASTER WHERE POS='RF')  AS T9

Open in new window


The problem I have is if there's more than 6 or 7 players at each position it takes forever to complete the query (I had to halt it), because there's too many permutations. And I actually don't need to list all players, I just need to select from it.

Is there any way I can optimize this to use more players? Would MS ACCESS be too slow?
Comment
Watch Question
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 5 Answers and 15 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE