CODEWITHSUNDEEP
sqlsql-servert-sql
Thiyagu
Thiyagu

Reputation: 1340

OR Operator in WHERE Clause takes more time for Execution

While adding OR operator in WHERE Clause it takes more time for execution to complete.

I am using two tables in my Stored Procedure.

I need to check two conditions in my query and which is also very simple.

But unfortunately we are unable to create Index for these table columns.

SELECT *
FROM TBL1
INNER JOIN TBL2 ON TBL1.ID=TBL2.ID
WHERE (@Code = TBL1.Code OR @Code IS NULL)
AND (@Port = TBL1.POL OR @Port = TBL1.POD OR @Port IS NULL)

Even though, I checked twice without these conditions it takes 0.35 sec with 9k rows

But when it add these conditions in where clause it takes more than 2 min.

Note: Sorry I'm unable to provide sample data for this issue.

Upvotes: 0

Views: 1029

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I would suggest that you use dynamic SQL:

declare @sql nvarchar(max);

set @sql = N'
SELECT *
FROM TBL1 INNER JOIN
     TBL2
     ON TBL1.ID = TBL2.ID
WHERE [where]
;

declare @where nvarchar(max)';

set @where = @where + (case when @code is not null then N'tbl1.code = @code and' else '' end);
set @where = @where + (case when @port is  not null then N'@port in (tbl1.pol, tbl1.pod) and'

set @sql = @sql + @where + N' 1=1';

exec sp_executesql @sql,
                   N'@code <type here>; @port <type here>',
                   @code=@code, @port=@port;

This generates only the code that is needed, so it will be optimized correctly.

The overhead of recompiling the query should be minimal compared to the time used for running the query (unless the two tables are trivially small).

Upvotes: 1

Moe Sisko
Moe Sisko

Reputation: 12041

You could try something like this, using CASE:

SELECT *
FROM TBL1
INNER JOIN TBL2 ON TBL1.ID=TBL2.ID
WHERE 1 = (CASE WHEN @Code IS NULL THEN 1 
                WHEN @Code = TBL1.Code THEN 1 END)
AND 1 = (CASE WHEN @Port IS NULL THEN 1 
              WHEN @Port = TBL1.POL THEN 1 
              WHEN @Port = TBL1.POD THEN 1 END)

I've sometimes experienced using "OR" to be the slowest alternative. YMMV. You could also try query hints.

Upvotes: 2

Dale K
Dale K

Reputation: 27289

If you absolutely have no option to add indexes you can sometimes improve OR performance by converting your query to use UNION ALL - which if I have got it right gives the following 6 components to cover all permutations.

SELECT *
FROM TBL1
INNER JOIN TBL2 ON TBL1.ID = TBL2.ID
WHERE @Code = TBL1.Code AND @Port = TBL1.POL

UNION ALL

SELECT *
FROM TBL1
INNER JOIN TBL2 ON TBL1.ID = TBL2.ID
WHERE @Code = TBL1.Code AND @Port = TBL1.POD

UNION ALL

SELECT *
FROM TBL1
INNER JOIN TBL2 ON TBL1.ID = TBL2.ID
WHERE @Code = TBL1.Code AND @Port IS NULL

UNION ALL

SELECT *
FROM TBL1
INNER JOIN TBL2 ON TBL1.ID = TBL2.ID
WHERE @Code IS NULL AND @Port = TBL1.POL

UNION ALL

SELECT *
FROM TBL1
INNER JOIN TBL2 ON TBL1.ID = TBL2.ID
WHERE @Code IS NULL AND @Port = TBL1.POD

UNION ALL

SELECT *
FROM TBL1
INNER JOIN TBL2 ON TBL1.ID = TBL2.ID
WHERE @Code IS NULL AND @Port IS NULL;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521979

You may try adding the following index to the first table:

CREATE INDEX idx1 ON TBL1 (Code, POL, POD, ID);

Assuming SQL Server would use the above index, it would execute the query by scanning TBL2 but using the index for the join and where restrictions. You may also try this version of the index:

CREATE INDEX idx1 ON TBL1 (ID, Code, POL, POD);

Upvotes: 1

Related Questions