查询性能和执行计划 [英] Query performance and execution plan

问题描述

大家好，



我在sql中有一个非常长的查询，带有一些嵌套的if else条件。基本语法如下所示。

Hi All,

i have a very long query in sql with some nested if else conditions. basic syntax is like below.

if @somevarialbe = 'A'
BEGIN
Select something from table whre condition = 'A'
END
ELSE 
BEGIN 
IF @othervariable = 'B'
BEGIN
Select something from table whre condition = 'B'
END
ELSE 
BEGIN
Select something from table whre condition = 'C'
END
END

基本上我的查询非常长且相同，除了这些条件，我不知道我怎么能以更好的方式优化这一点



我尝试过：



i尝试使用exec并创建一个动态查询来执行我的查询。但是查询仍在同一时间为不同条件获取大约50000条记录。



DECLARE @strQuery varchar（max）

SET @strQuery ='从表中选择一些'



DECLARE @WhereCondition varchar（500）



if @somevariable ='A'

BEGIN

SET @WhereCondition ='condition = A'



Exec CONCAT（@strQuery，@ WhereCondition）

END

basically my query is very long and identical except these conditions, i am not sure how can i optimize this in a better way

What I have tried:

i have try using exec and create a dynamic query to execute my query. but still the query is taking the same time to fetch around 50000 records for different conditions.

DECLARE @strQuery varchar(max)
SET @strQuery = 'SELECT something from table where '

DECLARE @WhereCondition varchar(500)

if @somevariable = 'A'
BEGIN
SET @WhereCondition = 'condition = A'

Exec CONCAT(@strQuery ,@WhereCondition)
END

推荐答案

整个事情可以替换为

The whole thing can be replaced with

SELECT something from table where condition = @somevariable

考虑在条件上放置一个索引如果查询需要一段时间。



但是，如果没有看到其余的查询，我们无法超越一般建议。看看这些CodeProject文章



在SQL Server中优化数据访问的十大步骤：第一部分（使用索引） [ ^ ]

SQL Server性能提示和指南 [ ^ ]

如何分析SQL Server性能 [ ^ ]

Consider putting an index on condition if the query is taking a while.

However without seeing the rest of your query we can't help beyond general advice. Have a look at these CodeProject articles

Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
SQL Server Performance Tips and Guidelines[^]
How to analyse SQL Server performance[^]

这篇关于查询性能和执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！