如果表有大约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屋!