We help IT Professionals succeed at work.
Get Started

Advice on building a query with nearly 6 million records

Benyaw616 asked
Last Modified: 2016-06-20
Hi All

i'm after some advice on the best way to avoid building a query with 6 million records (or close to that)

i have a logistics matrix to build with 3 points, collection point (40,000 of these) Load Port (8 of these) and destination port (650 of these) from 6 different suppliers.

what i am trying to do is work out the cheapest price, there are 2 tables involved.

1st table has 167k records, it's a list of haulage prices from towns in the UK, each supplier will provide a price from most town to every load port, 2nd table has the price from Load Port to destination port.

however many suppliers subsides the haulage tarriff so the supplier with the cheapest sea freight isn't always the cheapest overall (as another company's haulage maybe alot cheaper)

for example..

1. ABC Ltd. will charge $300 to collect from London and $500 to ship to New York = $800
2. DEF Ltd will charge $400 to collect from London and and $450 to ship to New York = $850
3. GHI Ltd will charge $250 to collect from London and $525 to Ship to New York = $775

therefore GHI is the better option.

ideally the user will just put in from London to New York and my data will show option 3 as the one.
Watch Question
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
This problem has been solved!
Unlock 2 Answers and 5 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