How can optimizing select query in a Cartesian product of the same table? Is there any other way to solve my business scenario?
Am designing an application that assign container with specified weight and destination to cargo trucks with specified truck capacity to a specified destination.
My design of tables in SQL server 2008 looks the following:
1. I have a table named ‘tblTrucks’ that holds truck information like [TruckPlateNumber,TrailerNumber,Transporter,TruckCapacity,TruckType,RegistrationDate] Truck capacity values are 250,300,350,420,450,500,550,600,650 and Truck types values are two High Bed(can load one 40ft container or two 20ft containers) and Truck Trailer(Can load only two 20ft containers).
2. I have a table name ‘tblContainerInformation’ that holds container information like [ContainerNumber,ContainerSize,ContainerDestination,Weight,DischargeDate,CargoType,status].Container size values are 20 and 40. This table holds a maximum of 20,000 records at a time.
My application needs a Cartesian product of ‘tblContainerInformation’ to combine two different 20ft containers in order to assign for the trucks based on the capacity of the truck. What my application do is it will load the truck information based on their registration date and iterate every record to find two containers that meets the truck capacity and destination. When it finds it updates the container status to assigned.
When the Cartesian product is done it takes a lot of time. So many records; if you have 10,000 records you will have 100,000,000. It makes my application very slow. Besides the select query have to be sorted the combined discharge date. (Two Dates)
Therefore my question here is;
1. Is there a better way to solve the business scenario other than Cartesian product?
2. If not how can I optimize the query performance in the Cartesian product and make the query run faster?
Your help is appreciated. Thanks.
Mesfin Ayalew
Submit an article or tip