Poor performance while using Union/Intersect on bulk data

Question

Poor performance while using Union/Intersect on bulk data

gaurav.kothari

Mr or Mrs. 500

Points: 515
More actions
January 21, 2010 at 8:18 am

#215751

Hi sql freaks,
I am having a query related to union and intersect operator when applying to bulk data.
i am having query like
select xID from table where SomeXColumn = 3 and SomeYColumn = 4
Union
select xID from table where SomeXColumn = 5 and SomeYColumn = 6
Union
select xID from table where SomeXColumn = 7and SomeYColumn = 8
Union
select xID from table where SomeXColumn = 9 and SomeYColumn = 10
---------------------------------------------------------------
Here is the details when each select statement is run separately.
Select Stmnt--->Record Return--->Time
Stmt 1--->10118--->00.00s
Stmt 2--->17134--->00.00s
Stmt 3--->16418--->00.00s
Stmt 4--->11114--->00.00s
------------------------------------------------------------------
when i run the above quey as a whole it is taking a tremendous time nearly 1 minute and 17 sec.
CAN ANY ONE help me out .
Thanks in advance,
Gaurav Kothari

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

One reason could be that you're using UNION instead of UNION ALL.

When combining two queries using UNION you'd force an (internal) DISTINCT to be applied to the result set, usually taking quite some time.

When using UNION ALL you'd get the results of each query, including dups.

It depends on the statements you use: If you can guarantee that there will be no duplicates by the nature of the query or if you can accept dups then use UNION ALL instead of UNION.

You can verify how much the sort operation will consume you could compare the actual execution plan of each statement.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

gaurav.kothari Mr or Mrs. 500 Points: 515 More actions · Answer 2

First of all thanks Lutz for the suggestion.

The reason why i used union is that i dont want to have duplicates.

Also, i am having smillar query with 8 intersect operators between different Select statemnts.

i checked the Query plan and found that clustered index scanning is taking 27% time.

A Quick help and good suggestion is appreciated,

Thanks again.

Gaurav Kothari

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 3

Do you have indexes on the other two columns?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gaurav.kothari Mr or Mrs. 500 Points: 515 More actions · Answer 4

Jeff,

i have clustered index on the tables primary key column.

and among all this three columns there is no primary key column.

--Gaurav

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 5

If I understand correctly, that may be the problem... no indexes on the columns you're working with.

If you could gen and capture an actual execution plan, I'm sure someone here would be able to make a better recommendation as to what your plight may be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Jeff Moden (1/22/2010)
If I understand correctly, that may be the problem... no indexes on the columns you're working with.
If you could gen and capture an actual execution plan, I'm sure someone here would be able to make a better recommendation as to what your plight may be.

Many people here are very good with execution plans. It is likely due to the indexes as Jeff has said.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gaurav.kothari Mr or Mrs. 500 Points: 515 More actions · Answer 7

Hi Jeff/Jason,

First of all thank to you guys, for propmptly replying to the Query.

I checked out the Execution plan on your suggestion (i generally dont do it, but from now onwards i will firstly look at it always) and found that the index scan was taking too much time.

i created a table with proper indexes and now the Query time has been reduced to 9 seconds from a magnanimous 2 mins 45 sec.

i am still trying on certain more areas to make it more effective.

Thanks a lot again for all your suggestion and replies.

--Gaurav

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

You're welcome. Glad to be of assistance.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events