SQL Server Performance

Indexes in two tables

Discussion in 'Performance Tuning for DBAs' started by danielreber, Feb 18, 2004.

  1. danielreber New Member

    I have two tables, one has 75k rows (date dimension) and the other has 10 million (fact table). The fact table has many columns with a value from the unique key of the date dimension (no RI defined). All of these columns in the fact table are indexed. The date dimension table does not have any indexes on it (no primary key either). If all date constraints are done on the date dimension table, will the indexes in the fact table be able to be used?

    Thanks


    Daniel Reber
    Datamasters, Inc
  2. joechang New Member

    databases generally do not require relations be defined to use indexes on joins,
    in DW, the data should have been scrubbed at load time, so integrity is not an issue.
    it may not hurt to have indexes in the date dim table,
    you will have to show the actual query along with the current execution plan to determine whether indexes are actually used in a beneficial manner
  3. danielreber New Member

    "it may not hurt to have indexes in the date dim table"

    Does that mean that it could hurt?

    Thanks

    Dan
  4. joechang New Member

    sorry, i was unclear
    i am assuming the you insert to the fact table periodically and almost never insert in the dim tables,
    that means you can put as many indexes on the dim tables as you please,
    even if you periodically modify the dim tables, 75k rows isn't that many, so it really doesn't matter
    but i do need to see your queries to determine if the indexes will actually get used, sql server uses some seriously out-of-date formulas in determining when to use indexes.
    see my article large data ops or something like that, coming soon on this site

Share This Page