CODEWITHSUNDEEP
sqlsql-server
James
James

Reputation: 1945

Performance of inserting rows with a UNION

How can I optimize the below query. You can see the only difference is with the Amount column.

INSERT INTO TableA
( Name, Amount, Cityname)

(SELECT Name,(Expense *15),CityName
FROM TableB B JOIN TableC C ON B.Id = C.FID AND B.Number = E23)
UNION
(SELECT Name,(Expense *25),CityName
FROM TableB B JOIN TableC C ON B.Id = C.FID AND B.Number = E23)

Are there any better ways to do this?

Upvotes: 1

Views: 84

Answers (3)

Maverick Sachin
Maverick Sachin

Reputation: 883

Try this query --

DECLARE @Name VARCHAR(255)
,@Expense1 INT
,@Expense2 INT
,@CityName VARCHAR(255)

SELECT @Name = [Name]
    ,@Expense1 = (Expense * 15)
    ,@Expense2 = (Expense * 15)
    ,@CityName = CityName
FROM TableB B
JOIN TableC C ON B.Id = C.FID
    AND B.Number = E23

INSERT INTO TableA (
    NAME
    ,Amount
    ,Cityname
)
VALUES (
    @Name
    ,@Expense1
    ,@CityName
)
,(
    @Name
   ,@Expense2
    ,@CityName
 )

And also you should consider the two points mentioned by @TabAlleman in the answer. I'm just repeating here what he said --

  1. Covering Indexes. Index TableB on Id, Number. Index TableC on FID.
  2. UNION ALL. If you aren't expecting any duplicate data that needs to be eliminated, then UNION ALL performs faster than UNION, since it won't do the extra work of checking for duplicates.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

I would suggest:

INSERT INTO TableA ( Name, Amount, Cityname)
    SELECT Name, (Expense * v.val), CityName
    FROM TableB B JOIN
         TableC C
         ON B.Id = C.FID AND B.Number = E23 CROSS APPLY
         (VALUES (15), (25)) c(val);

This is not exactly the same, because it does not reduce duplicates. But, I doubt that you really want to do that.

Then, you want indexes on TableB(Number, ID) and TableC(FID).

Upvotes: 4

Tab Alleman
Tab Alleman

Reputation: 31785

Two things you can do.

  1. Covering Indexes. Index TableB on Id,Number. Index TableC on FID.

  2. UNION ALL. If you aren't expecting any duplicate data that needs to be eliminated, then UNION ALL performs faster than UNION, since it won't do the extra work of checking for duplicates.

Upvotes: 1

Related Questions