topic Re: Performance impact when splitting one large table into 2 or more in App Development

Performance impact when splitting one large table into 2 or more

Mauritz_SA — Fri, 05 Apr 2019 12:10:18 GMT

Hi everyone

I have a question regarding self service and the impact of splitting large tables into two (or three for that matter). We have a self service solution where we as the BI department provide standard tables in an app and show certain KPIs which are important to the company. The standard tables contain a few columns (let's say 10) of the actual SQL tables (with about 100 columns and many text fields). I was wondering what the impact would be of breaking one such 100 column table into two or more tables which share a common ID field (1 to 1 relationship) and then concatenating them all?

Current:

Machines:

Id	Code	Description	General description	Value	Capacity	Make	Model
1	TP001	Tray packer	Tray packer on line A2	$200 000	1500	Make1	AAA
2	FM001	Filling machine	Grey filling machine on line A2 with automatic splicer	$450 000	5000	Make1	BBB

What I am planning:

Machines:

Id	Code	Value	Capacity
1	TP001	$200 000	1500
2	FM001	$450 000	5000

Machine Details:

Id	Description	General description	Make	Model
1	Tray packer	Tray packer on line A2	Make1	AAA
2	Filling machine	Grey filling machine on line A2 with automatic splicer	Make1	BBB

This forms part of a larger data model which is actually a snow flake schema. For the standard apps we only need the Machines table (reduced one), but I would like to make the Machine Details available to the end users as well when they need to do that kind of analysis.

Obviously this is a simplified example, but I would just like to get the experts' opinion on the principle of splitting a big table into two and how that will compare to having one large table. Maybe @swuehl or @Gysbert_Wassenaar have some ideas?

Regards,

Mauritz

Re: Performance impact when splitting one large table into 2 or more

swuehl — Fri, 05 Apr 2019 12:08:53 GMT

Your ID field is not a primary key on your current table.

Splitting the tables and using ID as key field will link tray packer to both codes, for example.

Is this what you want and correct?

Re: Performance impact when splitting one large table into 2 or more

Mauritz_SA — Fri, 05 Apr 2019 12:15:41 GMT

Hi Stefan

I am sorry. The ID column is actually unique. I changed it in red in my original post. My question is actually what the effect would be of splitting a large table with for example 101 columns (each row with a unique IDs) into two tables. One with 21 columns (ID + 20 columns) and another with 81 columns (ID + 80 columns). Would loading and concatenating these two tables (as a user would do when they add tables using the data manager) be a lot more resource intensive than just loading the table with 101 columns?

Regards,

Mauritz