7
When modeling a database, the number of columns can interfere with performance?
Ex: Tabela1 possui 2 campos, 1 int pk e 1 nvarchar(50)
Tabela2 possui 50 campos, 1 int pk e 49 nvarchar(50)
Select used for both tables, regardless of column size:
select Id,Nome from Tabela1
select Id,Nome from Tabela2
9
Everything can interfere with performance, but here comes the question of need or not. It certainly influences, but very little and proportional. Actually proportional amortized because some transactions will not make a difference how many columns have.
There are many much more important reasons that will influence performance.
Don’t worry about it. If you need a lot of columns, put them in. If you don’t have a reason for them to exist, you don’t have to create them. If you try to gather data in a column to have fewer columns, then I would say that it will further worsen performance and create a lot of confusion. Don’t do this.
Even if it has many columns, read only the ones you need in that query. This minimizes the impact.
Having a lot of data can indirectly influence performance because it can fill up the cache faster and have to access disk more often. But note that the size of the total data is more important than the number of columns.
thanks for answering, is that I see some cases, that create other tables and reference to FK, for example, Contactoid, Enderecoid ... Even if the table isn’t 1-N, then I got this question...
The reason is different. Separating columns in other tables can make the performance considerably worse. Of course, each case is a case. There are situations where this can improve performance in most cases and worsen in extreme cases, which can be a good trade. But you have to analyze the concrete case. There are people who do this because they think object-oriented and do not care about performance. Some people do it because they don’t know what they’re doing.
Browser other questions tagged sql sql-server performance
You are not signed in. Login or sign up in order to post.
as the bigown pointed out, the best strategy for inheritance, will depend on your need, in any case I advise you to read this comparative between TPH, TPT and TPC: How to Choose an Inheritance Strategy
– Tobias Mesquita
@Tobymosque opa.. then, related to inheritance in EF...I never used, until I tried once... But I preferred not to use to not get engaged...
– Rod
although the article refers to tips regarding EF, this chapter in specific applies to which wants ORM, it addresses precisely how the strategy of creating the tables affects the performance, integrity, flexibility and aesthetics... due to some limitations of some Orms, it is not interesting to have a table for each object, a single table representing the base object and its derivatives will present a better performance, against starting several tables can improve the validation of the data in the database, flexibility to include subtypes and its aesthetics.
– Tobias Mesquita