Avatar of intoxicated_curveball
intoxicated_curveball
 asked on

SQL Cartesian Product query with many permutations takes too long

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?
Microsoft AccessMicrosoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
intoxicated_curveball

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Kent Olsen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
intoxicated_curveball

ASKER
PatHartman: You're on to what I'm doing. Each player is assigned a 'performance' rating and a dollar value.

The reason I need all possible permutations for this to work is I need to get the MOST TOTAL performance from X dollar value.... it may involve taking any range of players. So I've actually been Selecting TOP 10 or so from the list of permutations.

Kdo: Agree but as I mentioned I don't need to list all the players just "select" from it. Still I think you're right. I knew the permutations were impossible. I'm still open to suggestions on how to improve performance or how to do this another way which allows me to add more players.
Kent Olsen

Hi CurveBall,

All you really need is 9 "dropdowns" or other selection objects.  List each player and his cost in his position box.  When a player is selected from a box, increment a Cost variable by the player's cost.  (You may have to decrement by a previous value if another player was already selected.)

You have a running total of total cost.  If it exceeds your budget, you need to choose a less expensive player at 1 or more positions until the price is affordable.


Populating 9 selection objects is nearly trivial.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kent Olsen

Hi Pat,

There are a lot of ways to do this.  I'd be very tempted to develop a heuristic program that assigns a weight to each position and selects players for all positions, based on a cost weight ratio.  That could be the starting point, with subjective changes to follow, or even the final result.  Adjust the weights, costs, and evaluations for the next round and do it again.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
intoxicated_curveball

ASKER
Hi unfortunately these proposed solutions will not work for me. Because I need to come up with the best team per $ value, it's really not possible to do it manually.
PatHartman

You don't have to do it manually.  Create a query that calculates the cost/value ratio for each player.  Assign a rank to each player based on that ratio.  Then create a query that selects players by rank.   A query that selects the first ranked player for each position will give you the "best" team for the money.    If you can't spend that much money, then chose lower ranked players for less important positions until you get down to your best team for the price you can spend.  If you can spend more money, choose players who have better stats but who cost more money relative to their stats.
intoxicated_curveball

ASKER
I've requested that this question be deleted for the following reason:

No solution given
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
intoxicated_curveball

ASKER
I've requested that this question be deleted for the following reason:

No solution
Kent Olsen

The answer is, "it's not practical and may not be possible".  That has been stated during the discussion.

Sometimes the answer is, "you can't do that".
PatHartman

Just because there is no solution you like, doesn't mean that having the discussion here has no value.  Simply close it and don't assign points to anyone since no one gave you a solution you want to adopt.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
intoxicated_curveball

ASKER
Where is close option?