2

I have this table of:

  • ~200k rows (expected to grow to about a million)
  • about 40 columns (might grow slightly)

The first 20 columns are used by all rows, but the remaining 20 columns are just used by about 2% of the rows, and null for the rest. Queries are run mainly on the first columns.

My question is if it has any performance benefits (or are any practical up/downsides) to split these out as a separate table with a 1:1 relationship. The columns are a mix of dates, integers, and short strings. Its going to run on Azure SQL and im using an ORM.

BobbyTables
  • 4,481
  • 1
  • 31
  • 39
  • doesn't seem like that would be an issue. Have you monitored/profiled it? are you experiencing any specific issues? – Tanner Apr 05 '16 at 08:49
  • thanks for the reply, i have not looked at numbers, im swapping DB technology so i have the opportunity to restructure. Some call it premature optimization... – BobbyTables Apr 05 '16 at 09:32

1 Answers1

3

Well, it depends. From storage engine perspactive, splitting the table will give you smaller rows in the main table, more rows on a single page, more data read in a single go, and probably a better perf as a result. But it largely depends on if the majority of your queries will join to the sub table or not. You should test both approaches, of course, and also take into consideration the sparse columns feature (https://msdn.microsoft.com/en-us/library/cc280604.aspx).

dean
  • 9,960
  • 2
  • 25
  • 26