如何提高SQL选择查询性能？ [英] How to Improve SQL Select Query performance?
问题描述
嗨Frnds，
我有一个SQL数据库表，有近2000万条记录。已经索引了4个列。我想选择表格的某些部分进行应用计算。
例如：SELECT Id，name，description FROM Table WHERE Active = 1
这需要大约2分钟才能产生结果。从SQL表中更快地检索数据并将其加载到DataSet（ASP.Net）中的任何其他方法？
请建议我任何搜索机制。像弹性搜索!!!!
Hi Frnds,
I am having a SQL Database table which have almost 20 million records. There are 4 Columns already indexed. I want to select some part of the table for application calculation.
For example: SELECT Id, name, description FROM Table WHERE Active = 1
This is taking around 2 minutes to produce the result. Any other method to retrieve the data faster from SQL table and load it in DataSet(ASP.Net)?
Please suggest me any searching mechanism. Something like Elastic search!!!!
推荐答案
这不是一件简单的事，有很多待办事项。检查这个答案，我已经解释了每个主题的内容。
如何提高网站的性能？ [ ^ ]
检查数据加载优化&针对您的问题进行数据库优化。
This is not a simple thing, there's a lot of todo. Check this answer, I have explained things on each topic.
How to improve performance of a website?[^]
Check Data Loading optimization & Database optimization for your issue.
1. Don't use UPDATE instead of CASE
This issue is very common, and though it's not hard to spot, many developers often overlook it because using UPDATE has a natural flow that seems logical.
2. Don't blindly reuse code
This issue is also very common. It's very easy to copy someone else's code because you know it pulls the data you need. The problem is that quite often it pulls much more data than you need, and developers rarely bother trimming it down, so they end up with a huge superset of data. This usually comes in the form of an extra outer join or an extra condition in the WHERE clause. You can get huge performance gains if you trim reused code to your exact needs.
<pre lang="sql">3. Do pull only the number of columns you need
This issue is similar to issue No. 2, but it's specific to columns. It's all too easy to code all your queries with SELECT * instead of listing the columns individually. The problem again is that it pulls more data than you need. I've seen this error dozens and dozens of times. A developer does a SELECT * query against a table with 120 columns and millions of rows, but winds up using only three to five of them. At that point, you're processing so much more data than you need it's a wonder the query returns at all. You're not only processing more data than you need, but you're also taking resources away from other processes.</pre>
4. Don't double-dip
Here's another one I've seen more times than I should have: A stored procedure is written to pull data from a table with hundreds of millions of rows. The developer needs customers who live in California and have incomes of more than
40 ， 000 。因此，他在 加利福尼亚州 中查询 客户 span>将结果放入 temp 表; 他查询 客户 以上收入
40,000. So he queries for customers that live in California and puts the results into a temp table; then he queries for customers with incomes above
这篇关于如何提高SQL选择查询性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
