I have a table that has transactions for a bus (boarding riders). Given the route ID and the date, I need to look up in another table what service type it was doing that day. Bus schedules change at most every 6 months or so, with most going years unchanged.
Currently the schedule table is defined like so:
CREATE TABLE [dbo].[Routes]( [ID] [int] NOT NULL, [RouteID] [int] NOT NULL, [Type] [varchar](50) NOT NULL, [StartDate] [datetime] NOT NULL, PRIMARY KEY CLUSTERED ( [ID] ASC ));
An example might look like:
ID RouteID Type StartDate -- ------- ------------ ---------- 1 301 Standard 2015-01-01 2 301 Discontinued 2016-06-01 3 302 Standard 2015-01-01 4 302 ParaTrans 2017-01-01
So, if I have a transaction from 2015-04-20 for
RouteID 301, I want to get back "Standard", but if the transaction is from 2018-01-20, it should return "Discontinued". For transactions prior to 2015-01-01, it should return NULL (or "", or anything other than a result that might conflict with a valid answer, i.e., "Standard", "Paratrans", or "Discontinued").
Basically, the table represents that route 301 was a standard route between 2015-01-01 and 2016-05-31 (and thus any transactions during that period should be categorized as "Standard"), then it was Discontinued on 2016-06-01 (through current day, implicitly as there is no later schedule change noted), while 302 was a Standard route from 2015-01-01 through 2016-12-31, then a ParaTrans(it) route after.
Route Type Start End ----- ---- ----- --- 301 Standard 2015-01-01 2016-05-31 Discontinued 2016-06-01 Present 302 Standard 2015-01-01 2016-12-31 ParaTrans 2017-01-01 Present
Currently, the query to do this looks like this:
SELECT TRANSIT_DAY, ROUTE_ID, (SELECT TOP (1) Type FROM Routes WHERE (RouteID = dbo.DAILY_SALES_DETAIL.ROUTE_ID) AND (StartDate <= dbo.DAILY_SALES_DETAIL.TRANSIT_DAY) ORDER BY StartDate DESC) AS NCTD_MODE FROM dbo.DAILY_SALES_DETAIL
What I'd like to know is: Is this the most effective combination of (a) structure of the
Routes table and (b) query to achieve this result? In other words, could a more efficient query be used with the existing structure? Could a change to the route table allow for a more efficient query?
The table of transactions is imported from a vendor on a daily basis, and so changing the schema of that table is not trivial and it is preferred to be avoided. More importantly, this lookup is used across a number of tables and databases using transactions or other bus related data sourced form multiple vendors; this is just a single example. We have one vendor (and thus one database) for monetary transactions, another for rider count, and still another for performance, and so on, with the route number and date being the only reliably consistent identifier across them all.
The route table has an index of
(RouteID, StartDate). Currently there are 56 rows in the Route table and 26M rows in the transaction table. The route table is comprised of 45 routes, and currently there are no routes with more than 2 rows, or one change. There is no limit to how many changes a single route could have, but I include this stat to show that the number is likely to remain small for the foreseeable future.
I can add any required indexes in order to optimize a suggested query. The question is more about finding the best strategy, assuming all reasonable optimizations are made to the considered strategies, than about finding the best optimization of a particular strategy.