问题描述
我有一个搜索过程,它传递了大约 15-20 个(可选)参数,搜索过程调用它们各自的函数来检查传入参数的值是否存在于数据库中.因此,它基本上是一个基于多个参数的 Search
结构.
I have a search procedure that is being passed around 15-20 (optional) parameters and the search procedure calls their respective functions to check if the value passed in parameter exists in the database. So, it is basically a Search
structure based on a number of parameters.
现在,由于数据库将有数百万条记录,我预计简单的简单搜索过程会立即失败.有哪些方法可以提高查询性能?
Now, since the database is going to have millions of records, I expect the simple plain search procedure to fail right away. What are the ways that can improve query performance?
到目前为止我尝试过的:
What I have tried so far:
FirstName
数据库列上的聚集索引(因为我希望它被非常频繁地使用)
Clustered index on
FirstName
column of database (as I expect it to be used very frequently)
Non Clustered index
基于用户搜索的其余列以及 include
关键字.
Non Clustered index
on rest of the columns that are basis of the user search and also the include
keyword.
注意:
我正在寻找更多方法来优化我的查询.
I am looking for more ways to optimize my queries.
大多数查询只不过是根据条件检查的 select 语句.
Most of the queries are nothing but select statements checked against a condition.
其中一个查询使用 GroupBy
子句.
One of the queries uses GroupBy
clause.
我还创建了一个 临时表
,我将在其中插入所有匹配的条目.
I have also created a temporary table
in which I am inserting all the matched entries.
推荐答案
First Run
query
from Sql Server Management Studio
并查看查询计划以查看瓶颈在哪里.您看到表扫描"或索引扫描"的任何地方都必须遍历所有数据才能找到要查找的内容.如果您创建可用于这些操作的适当索引,它应该会提高性能.
First Run
the query
from Sql Server Management Studio
and look at the query plan to see where the bottle neck is. Any place you see a "table scan" or "index scan" it has to go through all data to find what it is looking for. If you create appropriate indexes that can be used for these operations it should increase performance.
下面列出了一些提高 sql 查询性能的技巧.
避免在单个查询中进行多个连接
尽量避免使用包括外连接、交叉应用、外应用和其他复杂子查询在内的多个连接编写 SQL 查询.它减少了优化器决定连接顺序和连接类型的选择.有时,优化器被迫使用嵌套循环连接,而不管对具有过于复杂的交叉应用或子查询的查询的性能影响.
Try to avoid writing a SQL query using multiple joins that includes outer joins, cross apply, outer apply and other complex sub queries. It reduces the choices for Optimizer to decide the join order and join type. Sometime, Optimizer is forced to use nested loop joins, irrespective of the performance consequences for queries with excessively complex cross apply or sub queries.
从查询中消除游标尝试从查询中移除游标并使用基于集合的查询;基于集合的查询比基于游标的查询效率更高.如果需要使用游标而不是避免使用动态游标,因为它往往会限制查询优化器可用的计划选择.例如,动态游标限制优化器使用嵌套循环连接.
Eliminate Cursors from the QueryTry to remove cursors from the query and use set-based query; set-based query is more efficient than cursor-based. If there is a need to use cursor than avoid dynamic cursors as it tends to limit the choice of plans available to the query optimizer. For example, dynamic cursor limits the optimizer to using nested loop joins.
避免使用非相关标量子查询您可以重新编写查询以将不相关的标量子查询作为单独的查询而不是主查询的一部分删除,并将输出存储在一个变量中,该变量可以在主查询或批处理的后面部分引用.这将为优化器提供更好的选项,这可能有助于返回准确的基数估计以及更好的计划.
Avoid Use of Non-correlated Scalar Sub QueryYou can re-write your query to remove non-correlated scalar sub query as a separate query instead of part of the main query and store the output in a variable, which can be referred to in the main query or later part of the batch. This will give better options to Optimizer, which may help to return accurate cardinality estimates along with a better plan.
避免多语句表值函数 (TVF)多语句 TVF 比内联 TFV 成本更高.SQL Server 将内联 TFV 扩展到主查询中,就像它扩展视图一样,但在与主查询不同的上下文中评估多语句 TVF,并将多语句的结果具体化到临时工作表中.单独的上下文和工作表使多语句 TVF 成本高昂.
Avoid Multi-statement Table Valued Functions (TVFs)Multi-statement TVFs are more costly than inline TFVs. SQL Server expands inline TFVs into the main query like it expands views but evaluates multi-statement TVFs in a separate context from the main query and materializes the results of multi-statement into temporary work tables. The separate context and work table make multi-statement TVFs costly.
创建高选择性索引选择性定义了表中符合条件的行的百分比(符合条件的行数/总行数).如果符合条件的行数与总行数的比率较低,则索引具有高度选择性并且最有用.如果比率在 5% 左右或更小,则非聚集索引最有用,这意味着索引是否可以排除 95% 的行.如果索引返回表中超过 5% 的行,它可能不会被使用;要么选择或创建不同的索引,要么扫描表.
Create a Highly Selective IndexSelectivity define the percentage of qualifying rows in the table (qualifying number of rows/total number of rows). If the ratio of the qualifying number of rows to the total number of rows is low, the index is highly selective and is most useful. A non-clustered index is most useful if the ratio is around 5% or less, which means if the index can eliminate 95% of the rows from consideration. If index is returning more than 5% of the rows in a table, it probably will not be used; either a different index will be chosen or created or the table will be scanned.
在索引中定位一列索引中列的顺序或位置对于提高 SQL 查询性能也起着至关重要的作用.如果查询条件与索引键中最左侧的列相匹配,则索引有助于提高 SQL 查询性能.作为最佳实践,大多数选择性列应放在非聚集索引键的最左侧.
Position a Column in an IndexOrder or position of a column in an index also plays a vital role to improve SQL query performance. An index can help to improve the SQL query performance if the criteria of the query matches the columns that are left most in the index key. As a best practice, most selective columns should be placed leftmost in the key of a non-clustered index.
删除未使用的索引删除未使用的索引有助于在不影响数据检索的情况下加快数据修改速度.此外,您需要为不经常运行并使用某些索引的批处理定义策略.在这种情况下,在批处理之前创建索引,然后在批处理完成后删除它们有助于减少数据库开销.
Drop Unused IndexesDropping unused indexes can help to speed up data modifications without affecting data retrieval. Also, you need to define a strategy for batch processes that run infrequently and use certain indexes. In such cases, creating indexes in advance of batch processes and then dropping them when the batch processes are done helps to reduce the overhead on the database.
统计信息的创建和更新您需要处理查询中引用的计算列和多列的统计信息创建和定期更新;查询优化器使用有关表统计信息的一列或多列中值分布的信息来估计查询结果中的基数或行数.这些基数估计使查询优化器能够创建高质量的查询计划.
Statistic Creation and UpdatesYou need to take care of statistic creation and regular updates for computed columns and multi-columns referred in the query; the query optimizer uses information about the distribution of values in one or more columns of a table statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.
重新审视您的架构定义最后但并非最不重要的一点是,重新审视您的架构定义;注意适当的 FORIGEN KEY、NOT NULL 和 CEHCK 约束是否到位.在正确的地方使用正确的约束总是有助于提高查询性能,比如 FORIGEN KEY 约束通过将一些外部或半连接转换为内部连接来帮助简化连接,而 CHECK 约束也通过删除不必要或冗余的谓词来帮助一些.
Revisit Your Schema DefinitionsLast but not least, revisit your schema definitions; keep on eye out that appropriate FORIGEN KEY, NOT NULL and CEHCK constraints are in place or not. Availability of the right constraint on the right place always helps to improve the query performance, like FORIGEN KEY constraint helps to simplify joins by converting some outer or semi-joins to inner joins and CHECK constraint also helps a bit by removing unnecessary or redundant predicates.
这篇关于提高 SQL Server 查询性能的可能步骤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!