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/questions/28429630/Get-all-permutations-of-baseball-players-on-a-Team-in-SQL.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:
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?
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 T9The 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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
ASKER
I've requested that this question be deleted for the following reason:
No solution given
No solution given
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be deleted for the following reason:
No solution
No solution
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".
Sometimes the answer is, "you can't do that".
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.
ASKER
Where is close option?
ASKER
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.