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 miles is (end_miles - start_miles), ditto for gals.

Should we have those miles and 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
  • 1
    WRT what indexed are best, well how do you expect to query the table most often? What filters, join conditions, and sort orders? 3,000 records per week is nothing, so you have a lot of flexibility in picking indexes. Oct 14, 2011 at 22:31
  • Computed column is standard for how you're using it and should perform just the same as you doing the calculation yourself. If you query the calculated value often or want to index it, make sure it's a persisted column. Space in that case would be negligible. If it's not persisted then SQL Server just calculates it on the fly every time you request it. Oct 14, 2011 at 22:36
  • As I see, you need no chanhes in the table structure. All you need is right indexing strategy. Read these articles: Indexing strategies and General Tips on Optimizing SQL Server Indexes. I think, you need clustered index on start_date or end_date (I'd prefer start_date), and non-clustered on truck_num and mpg.
    – Alex_L
    Oct 14, 2011 at 23:09
  • I'd be joining on truck num and using filters on mpg and the dates. Possibly also the miles and fuel gallons.
    – Gabe
    Oct 14, 2011 at 23:37
  • Also see dba.stackexchange.com/questions/3831/… please
    – gbn
    Oct 15, 2011 at 14:31

2 Answers 2

  • Computed columns are your friend. Use them to capture simple calculations you're going to do anyway, and to guarantee that the computed values are correct.
  • Persist the results if you want to index or filter on them; don't persist them if you just need to pull the value once in a while.
  • Capture all data constraints using CHECK constraints.

Here is a pseudo-schema definition:

  id               PRIMARY KEY
, truck_num

, start_date
, end_date         CHECK (end_date > start_date)

, start_miles      CHECK (start_miles >= 0)
, end_miles        CHECK (end_miles > start_miles)

                   -- what if they refill the tank?
, start_gals       CHECK (start_gals >= 0)
, end_gals         CHECK (end_gals < start_gals AND end_gals >= 0)

-- all these should be computed
, miles = end_miles - start_miles
, gals = start_gals - end_gals
, mpg = miles/gals

As for indexing the table, here's what I think:

  • Cluster on start_date ASC. This will satisfy your WHERE clause. You are always inserting data by increasing start_date, meaning your writes will always be sequential under this clustering scheme. You are also always querying by start_date so you satisfy your biggest query pattern as well. (3,000 inserts per week is nothing. Because you have such a low volume of inserts, you could even cluster on start_date ASC, end_date ASC.)
  • Create a non-clustered index on truck_num and INCLUDE mpg. This should satisfy your SELECT, JOIN, and GROUP BY clauses. If you want to ORDER BY mpg, then make mpg part of the index key after truck_num instead of just INCLUDE-ing it.

When you're done, test your setup as follows:

  1. Create a test table
  2. Pump it full of test data
  3. Create the indexes
  4. Update statistics
  5. Run your most common queries
  6. Check their plans and run times

I like Nick's answer so won't repeat his info about computed columns and constraints.

I'd approach indexing differently:

  • If all NC indexes can be covering, then choice of clustered index doesn't matter so a surrogate would be OK.
  • This only matters at higher volumes/large databases and if query patterns require more NC indexes.
  • A clustered index in covering anyway.. so leading on from that, an index on (startdate, enddate, trucknum) must be unique (same truck can't do an identical journey). So make that clustered and unique: a classic PK using the natural key

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.