Question:
I have several tables with the same structure and I’m trying to combine them all into one unique table. But I’m worried about performance because each table has a lot of rows. If I make joins and complex selects, that would make the tables slower.
So, what is the better solution, keeping the multiple tables or joining them into one?
Answer:
Whether or not to keep this data in separate tables depends on a number of factors, such as how frequently the data is updated and how frequently it’s queried. For frequent updates and inserts, separate tables may be more efficient because each table will contain less data. Also, the indexes will be smaller with smaller tables.
To minimize the performance impact of multiple table joins, you can create views. Views are virtual tables based on a SQL statement. You can query views directly, just like tables, or through stored procedures. For example, if you create a view called Tables1Thru5 that joins tables 1 through 5, you can query like this:
SELECT * FROM Tables1Thru5
The best approach in this case is to create sample tables for each scenario and test both updates and queries to see which is the most efficient.
