CODEWITHSUNDEEP
sqlperformance
John
John

Reputation: 503

sql query performance - wide table with fewer rows or narrow table with lot of rows?

I would like to make queries on fact table in star schema.

I need to capture lot of values like gross sales net sales regional sales etc for combination of other couple values that is PK.

I have two options:

  1. One row with PK and lot of columns with measures like gross sales, regional sales etc.
  2. make measures as a dimension, so PK would be bigger - there would be added column Measures in row. And only one value beside PK. So I decompose the one row with many Measures to lot of rows with one Measure.

What is better for performance, both insert and select?

Upvotes: 3

Views: 1324

Answers (2)

Maess
Maess

Reputation: 4146

What is your lowest level of granularity in the fact? When you mention things like regional sales and gross net sales it makes me think that you might be confusing measures and dimensions. For example: a region would be a dimension of the sales fact.

Upvotes: 1

JNK
JNK

Reputation: 65157

You will have contention issues if you have a single row with all the values, assuming you have simultaneous inserts/updates and reads.

Having a single wide table also means it's much more difficult to add new measures in the future - it requires changing the table schema which will lock the table and cause other problems.

Your SELECT performance should be similar, unless you are pulling multiple values for the same PK in the same query, in which case the wider table would probably be a little quicker.

Upvotes: 3

Related Questions