Início / dba / Perguntas / 6836
Accepted
Gabe
Asked: 2011-10-15 13:53:28 +0800 CST

Melhor maneira de projetar esta tabela de quilometragem?

  • 772

Estarei preenchendo uma tabela de milhas por galão (MPG). Está vindo de uma fonte de hodômetro.

Atualmente está configurado da seguinte forma:

  id               (primary_key)
, truck_num
, start_date
, end_date
, start_miles
, end_miles
, start_fuel
, end_fuel
, miles
, gals
, mpg

Parece haver alguma redundância. O milesé (end_miles - start_miles), idem para gals.

Devemos ter essas colunas pré-calculadas e armazenadas no banco de dados miles? galsIsso definitivamente tornaria a consulta mais fácil, mas à custa de espaço. Mesma pergunta para ter o mpgcalculado. Uma coluna computada tornaria as coisas mais lentas, não?

Quais índices funcionariam melhor? São cerca de 3.000 caminhões (registros) inseridos em um lote toda semana.

Estou usando o SQL Server 2008 R2.

Editar: uma consulta de amostra que eu usaria

-- 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
sql-server-2008 database-design

2 respostas

  1. Best Answer
    • As colunas computadas são suas amigas. Use-os para capturar cálculos simples que você fará de qualquer maneira e para garantir que os valores calculados estejam corretos.
    • Persista os resultados se desejar indexá-los ou filtrá-los; não os persista se você só precisa puxar o valor de vez em quando.
    • Capture todas as restrições de dados usando CHECKrestrições.

    Aqui está uma definição de pseudo-esquema:

      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

    Quanto à indexação da tabela, eis o que penso:

    • Cluster em start_date ASC. Isso satisfará sua WHEREcláusula. Você está sempre inserindo dados aumentando start_date, o que significa que suas gravações sempre serão sequenciais nesse esquema de agrupamento. Você também está sempre consultando start_datepara satisfazer seu maior padrão de consulta. (3.000 inserções por semana não é nada. Como você tem um volume tão baixo de inserções, pode até agrupar em start_date ASC, end_date ASC.)
    • Crie um índice não clusterizado em truck_nume INCLUDE mpg. Isso deve satisfazer suas cláusulas SELECT, JOINe . GROUP BYSe você quiser ORDER BY mpg, faça mpgparte da chave de índice depois truck_num, em vez de apenas INCLUDEapertá-la.

    Quando terminar, teste sua configuração da seguinte maneira:

    1. Criar uma tabela de teste
    2. Bombeie-o cheio de dados de teste
    3. Crie os índices
    4. Atualizar estatísticas
    5. Execute suas consultas mais comuns
    6. Verifique seus planos e tempos de execução
    • 7

  2. Eu gosto da resposta de Nick, então não vou repetir suas informações sobre colunas computadas e restrições.

    Eu abordaria a indexação de maneira diferente:

    • Se todos os índices NC puderem ser cobertos, a escolha do índice clusterizado não importa, portanto, um substituto seria aceitável.
    • Isso só importa em volumes maiores/bancos de dados grandes e se os padrões de consulta exigirem mais índices NC.
    • Um índice agrupado na cobertura de qualquer maneira ... portanto, a partir disso, um índice (startdate, enddate, trucknum)deve ser exclusivo (o mesmo caminhão não pode fazer uma jornada idêntica). Então faça isso agrupado e único: um PK clássico usando a chave natural
    • 3

relate perguntas