SQL Optimization for Fast Retrieval of Transportation Route Data in TMS

Résumé

Transportation Management Systems (TMS) need regular and effective access to transportation data such as route cost, travel distance and CO₂ emissions to support route planning, freight pricing, dispatch scheduling and real time logistics operations. As transportation databases expand in size and query frequency increases, SQL query performance becomes a critical factor affecting overall system responsiveness. This study presents a standardized relational schema intended for query intensive TMS workloads and examines how SQL optimization techniques affect the efficiency of origin destination (OD) based transportation data retrieval. The proposed schema is assessed using an extensive synthetic transportation dataset through typical TMS style queries, including OD cost lookup, least cost route determination from a specified origin and emission restricted route filtering. Query performance is evaluated under baseline and indexed configurations using execution time measurements and analysis of query execution plans. The findings provide insights into how schema design and indexing strategies influence SQL execution efficiency for high frequency OD queries, providing pragmatic guidelines for the development of scalable and performance centric transportation databases.