增加Where语句的性能调优 [英] Increase the Performance Tuning for the Where Statement
问题描述
目标：
推荐和最佳的源代码实践，以便进行良好的性能调整。
Goal:
Recommended and best of practice of the source code in order to have a good performance tuning.
问题：
我不知道此代码是否是与性能相关的推荐方法。
它可能比下面的源代码更好的解决方案
Problem:
I do not know if this code is the recommended approach in relation to performance.
It might a better solution than this source code below
需要考虑的一些问题：
Enduser 1想要检索整个数据和标准（其中st atement）是活动的（isactive = 1）
Enduser 3希望数据符合条件（where语句）countryid（1和2），isactive（1），location（1-3）和categoryid（1）
等......
等......
等......
Some questions to take account to:
Enduser 1 wants to retrieve the whole data and the criteria(where statement) is active (isactive=1)
Enduser 2 wants the whole data with criteria(where statement) databaseint(1 and 2) and countryid(1 to 10)
Enduser 3 wants the data with criteria(where statement) countryid(1 and 2), isactive(1), location(1-3) and categoryid(1)
etc...
etc...
etc...
你可能会理解有许多不同的队列有不同的标准（where语句）。
You might understand that there are many and different quetions with different criteria(where statement).
主要问题是：
建议使用什么样的存储过程T-SQL代码，以便按顺序填写所有不同的问题及其标准有一个很好的性能调整？
The main question is:
What T-SQL code for the stored procedure is recommended in order to fullfill all the different questions and its criteria in order to have a good performance tuning?
信息：
*使用SQL Server 2018
*我只有源代码而不是数据。
* Ecommence网站有一个标准列表，例如购买一台电脑：多少内存，选择多个或单个处理器，品牌等。
Information:
*Using SQL server 2018
*I only have the sourcecode but not the data.
*Ecommence website have a criteria list for instance buying a computer: how much memory, select many or single processor, brand etc.
谢谢！
---------------
---------------
桌上人物
personid（int）
countryid（int）
firstname（nvarchar（50））
lastname（nvarchar（50））
age（int）
isactive（位）
databaseid（int）
locationid（id）
categoryid （int）
table person
personid(int)
countryid(int)
firstname(nvarchar(50))
lastname(nvarchar(50))
age(int)
isactive(bit)
databaseid(int)
locationid(id)
categoryid(int)
表格中总计10万行。
countryid =共30个不同的身份
isactive =只有1或0
databaseid =共6个不同的id
categoryid =共4个不同的id
locationid =完全5个不同的id
It is totallt 100 000 row in the table person.
countryid = totally 30 different id
isactive = only 1 or 0
databaseid = totally 6 different id
categoryid = totally 4 different id
locationid = totally 5 different id
--------
--------
表格中总计10万行。
countryid =共30个不同的身份
isactive =只有1或0
databaseid =共6个不同的id
categoryid =共4个不同的id
locationid =完全5个不同的id
It is totallt 100 000 row in the table person.
countryid = totally 30 different id
isactive = only 1 or 0
databaseid = totally 6 different id
categoryid = totally 4 different id
locationid = totally 5 different id
-----------------
-----------------
创建表[dbo]。[person]（
[personid] [int] NOT NULL，
[countryid] [int] NOT NULL，
[firstname] [nvarchar]（50）NOT NULL，
[lastname] [nvarchar]（50）NOT NULL，
[age] [int] NOT NULL，
[isactive] [bit] NOT NOT NULL，b
[databaseid] [int] NOT NULL，
[categoryid] [int] NOT NULL
）ON [PRIMARY]
GO
CREATE TABLE [dbo].[person](
[personid] [int] NOT NULL,
[countryid] [int] NOT NULL,
[firstname] [nvarchar](50) NOT NULL,
[lastname] [nvarchar](50) NOT NULL,
[age] [int] NOT NULL,
[isactive] [bit] NOT NULL,
[databaseid] [int] NOT NULL,
[categoryid] [int] NOT NULL
) ON [PRIMARY]
GO
推荐答案
它可能工作正常，但我怀疑它可能得到一些参数嗅探，即SQL Server查看第一个调用的参数并将它们用于下一次执行。向SELECT添加OPTION（RECOMPILE）很可能会解决这些问题。这迫使
SQL Server考虑所有在决定如何执行之前的参数，变量和表统计信息。
$
如果一个过滤器的组合更常见，那么处理它可能是一个好主意这种情况在一个单独的SELECT中使用IF语句来指导执行到最有效的SELECT。
花一些时间来确定哪些用例可能它们的发生频率和频率。然后在SQL Server Management Studio中运行测试并使用SET STATISTICS IO ON / OFF来了解不同情况的执行情况。
It might work just fine, but I suspect it may get some parameter sniffing, i.e. SQL Server looks at the parameters of the first call and uses them for the next executions. Adding OPTION(RECOMPILE) to the SELECT will most likely fix such issues. That forces SQL Server to consider all parameters, variables and table statistics before deciding how to execute.
If one combination of filters is much more common, it might be a good idea to handle that case in a separate SELECT using IF-statements to guide the execution to the most effective SELECT.
Take some time to figure out which use cases may occur and how frequent they would be. Then run tests in SQL Server Management Studio and use SET STATISTICS IO ON/OFF to get an idea of how the different cases perform.
这篇关于增加Where语句的性能调优的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
