Click here to Skip to main content
15,920,513 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

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

SQL
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


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
Posted
Updated 7-Mar-16 23:20pm

1 solution

The whole thing can be replaced with
SQL
SELECT something from table where condition = @somevariable

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[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900