如果表有大约40-50列，从SQL Server获取记录的最佳方法是什么？ [英] What is the best approach to get record from SQL Server if table has about 40-50 columns?

问题描述

我正在使用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!

这篇关于如果表有大约40-50列，从SQL Server获取记录的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！