CODEWITHSUNDEEP
sql
user7399003
user7399003

Reputation: 11

UNIONALL Performance

The UNIONALL is taking more time, below is example of query. as mentioned below table T1 having 4 records only, but in my query the T1 having almost 1.5 million records. Is there any way to tune below query means instead unionall can we use any condition. Thanks!

CREATE TABLE T1 (ID INT, FROM_KEY INT,TO_KEY INT, IS_STATUS BIT)

INSERT INTO T1 VALUES(1, 50001,50002, 1)
INSERT INTO T1 VALUES(2, 50003,50004, 1)
INSERT INTO T1 VALUES(3, 50005,50006, 1)
INSERT INTO T1 VALUES(4, 50007,50008, 1)

DECLARE @KEY INT = 50002

SELECT TO_KEY FROM T1 WHERE TO_KEY=@KEY AND IS_STATUS=1
UNION ALL
SELECT FROM_KEY FROM T1 WHERE FROM_KEY=@KEY AND IS_STATUS=1

Upvotes: 0

Views: 68

Answers (2)

jarlh
jarlh

Reputation: 44766

Can be simplified as:

SELECT @KEY
FROM T1
WHERE @KEY IN (TO_KEY, FROM_KEY)
  AND IS_STATUS = 1

(Will only return the same row once if both to_key and from_key are equal to @key at the same time, while the UNION ALL query would return that row twice.)

Upvotes: 1

serges_newj
serges_newj

Reputation: 815

Single pass query could be more optimal:

SELECT CASE WHEN N=0 THEN FROM_KEY ELSE TO_KEY END
FROM (SELECT * FROM T1 WHERE @KEY in (TO_KEY, FROM_KEY) AND IS_STATUS=1) AS A
    JOIN (SELECT 0 N UNION ALL SELECT 1) AS B
        ON (N=0 AND TO_KEY=@KEY) OR (N=1 AND FROM_KEY=@KEY)

But in general, if you wish to get help with query optimization, you have to provide much more information about sql server brand, version, data size, indexes and so on.

Upvotes: 0

Related Questions