如果表有大约40

本文介绍了如果表有大约40-50列，从SQL Server获取记录的最佳方法是什么？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用SQL Server来管理我的数据库，而且我有几个表，其中包含大量列，每列约40到50列。 现在我想从表中获取特定记录ASP.Net。我正在使用ADO.Net从SQL Server获取数据，我知道如何获取数据，但我正在寻找一些优化的方法来获取具有大量列的数据。

I am using SQL Server to manager my database and I have several tables which have large number of columns about 40 to 50 columns each. Now I want to get an specific record from the table using ASP.Net. I am using ADO.Net to get data from SQL Server and I know how can I fetch data but I am looking for some optimized approach to fetch data with large number of columns.

推荐答案

不确定在预留40-50列时会出现什么问题。根据应用程序的不同，一个表可以有数百个列并且表现非常好。 但是根据经验，你需要记住几件事：

• 永远不要使用*获取所有列，命名要获取的列。
• 仅获取所需的列。
• 永远不要在数据库端进行格式化，让客户端应用程序处理它。
• 尽可能基于密钥获取。
• 记住唯一索引密钥，最好使用primary
• 永远不要获取超过需要的行数。
• 如果提取了多行，请考虑分页。
Not sure what problems would you foresee in having 40-50 columns. Depending on the application a table can have hundreds of columns and perform very well. However as a rule of thumb few things you need to remember:
• Never fetch all columns using *, name the columns you want to fetch.
• Fetch only the columns you need.
• Never do formatting on the database side, let the client application handle that.
• Whenever possible fetch based on the key.
• Remember to uniquely index the key, preferably using primary key and unique constraints when needed.
• Never fetch more rows than needed.
• If several rows are fetched, consider paging.
如果你只使用有限数量的列，那么只检索你想要的那些：即不要使用 If you are only using a limited number of the columns, then only retrieve the ones you want: i.e. don't use SELECT * FROM MyTable WHERE ...

仅列出您的列感兴趣的是：

List only the columns you are interested in:

SELECT Column12, Column35 FROM MyTable WHERE ...

除此之外，调整WHERE子句只返回行你想要的几乎是你唯一的其他优化！

Other than that, tuning your WHERE clause to only return the rows you want is pretty much your only other optimisation!