Does a larger number of attributes in the table have performance degradation?

Question

So far, I have worked quite comfortably with the Windows C # application. I am going to upgrade to Asp.net for website development. The requirement made me place about 50 columns in one table. I know this concept of breaking it into small tables using regular forms.

I tried google search but get a lot of results. I need to know if my table with 50 attributes will reduce the performance of my web application? Can anyone suggest me about this.

Answer 1

Well, if you return them all, you probably have network costs (transferring data between db and your .NET code) and materialization costs (creating an object / DataTable in your DAL), so you would definitely have some overhead. In any case, you need to consider the size of the database page.

But perhaps the main thing: do you need all the data? If so, you can only do so much. Using multiple tables and implementing joins will also affect performance.

In most cases, and especially ASP.NET, the most important numbers are things like:

• What volume data actually comes into the client application? (can I use paging / ajax / etc.?)
• what data is stored in the session?
• How many rounds do you make between the client and the application server?

as the bandwidth and latency between your application and client tend to be a pinch point. Measure things; make sure you configure the right performance issues.

Additionally, ASP.NET has a reputation for not being shy about storing more than you expect in things like view-state; keep an eye on this or switch to a lighter model like ASP.NET MVC.

Answer 2

If you are talking about a database table with many fields (or columns), then 50 is not really unusual.

However, you must maintain the normalization of the database design, and if the design is normalized with 50 fields, go with that.

Answer 3

One table, 50 columns?

One point of normalization is to exclude inserting, deleting, updating anomalies

Now it will work like a dog with more than a few lines, but data integrity is superior to performance here ...

Answer 4

Instead of thinking about the number of columns, I suggest you think about the data types of the columns.

De-normalization is also popular. Choose a normalization based on your application logic. (Think about joins carefully)

Answer 5

it all comes down to the queries you will use. At the end of the day, this is the amount of data that you extracted from / writing to the table. if most of your queries are retrieved from / writing to most columns, yes, none of the columns will affect.

the turnaround time of your request will be directly proportional to the amount of data that it reads / writes. the larger the amount of data, the longer it will take. a large number of columns can mean a large amount of data (but not always).

saying that 50 columns are not a big number. I came across tables with over 300 columns. but then it also depends on the dbms you use.

Answer 6

It depends on what and how the data is retrieved. Of course, SELECT * will talk about performance. You will need to select the necessary columns and try to use the where clause. This is one way to make a table with lots of columns and data.

Answer 7

"The simplest thing that could work." (Ward Cunningham).

If all columns represent separate data elements, you follow good normalization rules, and you do not have groups of repeating elements, then the number of columns in the table does not really matter. If you want you to be able to start sweating by the size of the rows compared to the size of the data block, how much space you can or cannot waste, etc., but in my experience it is better to store your data together in one table if it doesn’t exist some irresistible functional reason why it should be divided into several tables. Fortunately, I had to work with databases where someone assumed that too many fields in one table were bad, so they logically broke one table into several tables with fewer fields. This was a nightmare when trying to make updates.

Good luck.

Answer 8

Depending on what you really mean, this may or may not be a problem.

If the 50 columns are relatively small in size and each contains a different type of data (phone, cit, state, name, etc.), you are probably fine.

If these are things like phone1, telephone2, etc., you need a linked table, as it is difficult to maintain and query correctly. For example, suppose you now have fifty phone numbers, and the day comes when you need 51, then you need to change the structure of the table and all the queries related to it (you do not use select * during production?) Suppose you want To find out who has a phone number 111-111-1111, you need to join (or combine) the table 50 times to get an answer. This can damage performance.

In the third case, 50 columns are different from each other, but they will all be a large record due to the size of the fields. Understand that databases will allow you to create a structure that is larger than the maximum number of bytes a record can contain, it simply will not allow you to put more than that number of bytes in a record. Plus, longer records tend to create problems with how data is stored on disk and can lead to slower data retrieval. In this case, it is best to create one or more related tables that will have a one-to-one relationship with the main table.

Answer 9

The thing that matters is not the number of columns in the table, but the "width" of the table.

For example, if all 50 columns of a column are bits , you are looking at 7 bytes in a row that is tiny .

On the other hand, if all 50 columns are VARCHAR(4000) columns, then you are looking at a potential maximum row size of about 200 MB per row (yes, SQL Server will allow you to do this), which obviously can cause problems (actually this probably won't, but I want to say that the width of the data matters, not the number of columns).

just a surefire way to find out if you have problems, try and see, but it’s usually very general , it’s nice to try to keep the line size below 4 KB (1 page), however this is a very general rule:

• Usually, you probably want your line size to be much smaller than this so that you can put many lines on the page
• However, if you have several large object fields (for example, VARCHAR or VARCHAR(MAX) ), the size of the rows in your table is likely to exceed 4 KB quite regularly, this is normal.

A complex subject - as I said, the only way to find out is to try it and see if it performs.

Note that with the exception of large objects (such as VARCHAR ), SQL Server will not allow you to create a row larger than 1 page.

---

Why can there be a problem with a "large" table?

Because it increases the amount of data that needs to be read.

As a very simple / far-fetched example, suppose you have a table sorted by identifier (i.e., has a clustered index for ID), and you want to get records for identifiers from 100 to 110 inclusive. If the line size is small (say 200 bytes), then the size of all these records is about 2 KB, which is much smaller than the page size (4 KB). Since the table is sorted by identifier, it is very likely that these records are suitable for one page, no more than 2, and therefore it takes only a few reads to read all 10 records.

Now suppose the row size is larger (say 2 KB), then the total size of all these records is now 20 KB. The minimum number of reads required is a minimum of 5, possibly 6. On a busy database server, this data is added to the additional I / O and memory overhead in cahce.

Large objects

Depending on the amount of data stored, the fields of large objects and variable lengths (for example, VARCHAR ) can store data on separate pages, or on pages of large pages, or on page overflow pages.

What does it mean? Well, if you have a table with many such columns, and you execute a SELECT * ... query, then SQL Server should get all these extra pages to read all this extra data. We ended up with the same situation as above - a lot of posts that are bad.

However, if instead we specify only some of the columns in our query, for example. SELECT ID, Address ... then SQL Server does not need to worry about reading pages containing data that we are not interested in. Despite the fact that this table can define many columns with a huge row width, since we indicated the columns of interest to us and because this data is stored on separate pages, the number of readings required is still relatively small.