Combine large tables into one dataset

Question

Post reply

Combine large tables into one dataset

PatrickG

Valued Member

Points: 63
More actions
September 7, 2022 at 11:01 am

#4084837
Hello everyone,
I'm facing a small challenge. I have four different data pots and need to merge them into one data set. The join at the database level is generally not a problem, but the performance is sub-par.
The four tables are as follows.
- projectStructure (65,527 rows)
- cashManagement (261,135 rows)
- bookedHours (48,233 rows)
- budgetValues (35,836 rows)
The key that connects all tables together is WBS_Element. The key is included in all tables. The table projectStructure is the "master table". The table projectStructure contains each key only once (i.e. 4450-00-01). In all other tables there can be multiple rows with the same key (i.e. 4450-00-01).
My problem now is to create a single dataset from this so that I can display all columns from all pots in a single table. So far I've tried looking up multiple datasets. However, this is very time-consuming and I cannot display several data sets for the same key in individual lines. It only works with a "trick" using the join function and then the data records are only displayed one below the other in one line.
Do you know of a simpler, more performant solution?
Best regards
Patrick
September 9, 2022 at 9:37 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

Quick question, are the data sets in a relational database, i.e. SQL Server?

😎

PatrickG Valued Member Points: 63 More actions · Answer 2

Hi Eirikur,

yes, they are based in a relational database. In my case it is SQL Server.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Hi Patrick,

can you post the DDL (create table) for the tables, and sample data as an insert statement?

😎

Combining the datasets on the RDBMS is much more efficient than attempting such an exercise in SSRS, especially with such a low cardinality of the sets and no cartesian procuce.

Jason Wilks Old Hand Points: 389 More actions · Answer 4

You will need to provide more info in order to get help with this. Include DDL that includes all tables, constraints, in indexes, etc. and it’s not clear whatyour expected output is. Providing a clear output sample will help. And Checkout this link too

https://www.sqlservercentral.com/articles/how-to-post-performance-problems-1