I will be populating a Miles Per Gallon (MPG) table. It's coming from an odometer source.
It's currently set up as so:
id (primary_key) , truck_num , start_date , end_date , start_miles , end_miles , start_fuel , end_fuel , miles , gals , mpg
There seems to be some redundancy. The
(end_miles - start_miles), ditto for
Should we have those
gals columns precalculated and stored in the database? It would definitely make querying easier, but at the expense of space. Same question for having the
mpg calculated. A computed column would slow things down, no?
What indexes would work best? There's about 3,000 trucks (records) inserted in a batch every week.
I'm using SQL Server 2008 R2.
Edit: A sample query that would I'd be using
-- find average mpg for since ytd select m.truck_num, avg(mpg) from mpg m join truck t on t.truck_num = m.truck_num where start_date >= @begin_of_year and end_date <= @today group by truck_num