SQL Query Question on Union All

Question

Post reply

SQL Query Question on Union All

cvvikram 71819

SSC Enthusiast

Points: 178
More actions
April 22, 2010 at 10:17 pm

#386769

Hi Friends,
I have a query as below
INSERT INTO DATABASE..STAG_TEMP_TABLE
SELECT ABC,SUM(ZXC)ZXC,SUM(XYZ)XYZ
FROM
(Select * from DATABASENAME..WWW (NOLOCK)
Union all
Select * from DATABASENAME..WWW1 (NOLOCK)) a
WHERE a.A1 BETWEEN '2009-10-09' AND '2010-04-08' AND a.A2 = 130 AND a.A3 > 10000
GROUP BY A2, ABC
This query is working as per the business, but is extremely slow(taking around 7-10Min). There are millions of records in the UNION All Tables.
Please let me know if there is any alternative approach for the above query.
Thanks

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

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

Ken McKelvey SSCoach Points: 18843 More actions · Answer 1

INSERT INTO DATABASE..STAG_TEMP_TABLE

SELECT ABC,SUM(ZXC)ZXC,SUM(XYZ)XYZ

FROM

(

-- Filter data here as the result will be passed up.

-- These two selects may also run in parallel.

SELECT *

FROM DATABASENAME..WWW

WHERE A1 BETWEEN '20091009' AND '20100408'

AND A2 = 130

AND A3 > 10000

UNION ALL

SELECT *

FROM DATABASENAME..WWW1

WHERE A1 BETWEEN '20091009' AND '20100408'

AND A2 = 130

AND A3 > 10000

) a

GROUP BY A2, ABC

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

What does the execution plan look like?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Also post table and index definition

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 4

Do both WWW and WWW1 have indexes on ABC, A1, A2 and A3? And updated statistics?

Depending on how the tables are being used, it may or may not be wise to have those indexes, but they would (probably) speed up the select queries (and slow down inserts). I say 'probably' because it would depend on cardinality and distribution of the data.

As Mr. Fritchey said, what do your execution plans look like? That would tell a lot.

Rob Schripsema
Propack, Inc.

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

Also, is there a chance that WWW and WWW1 are actually views. Depending on the content of those views, that could really gum up the works, as well.

Last but not least, the use of SELECT * will pretty much guarantee a table scan or its near equivalent INDEX SCAN. Almost always limit your SELECTs to only those columns actually needed. Yes, there are some exceptions to that rule... but this isn't one of them. 😉

--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)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

Jeff Moden (4/23/2010)
Also, is there a chance that WWW and WWW1 are actually views.

Why do you think that it could be view ? because of "Select * " ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

vidya_pande SSCertifiable Points: 5275 More actions · Answer 7

Try this one? In certain circumtances this can improve the performance..

INSERT INTO DATABASE..STAG_TEMP_TABLE

SELECT ABC,SUM(ZXC)ZXC,SUM(XYZ)XYZ

FROM

(

SELECT ABC,SUM(ZXC)ZXC,SUM(XYZ)XYZ

FROM DATABASENAME..WWW

WHERE A1 BETWEEN '20091009' AND '20100408'

AND A2 = 130

AND A3 > 10000

GROUP BY A2, ABC

UNION ALL

SELECT ABC,SUM(ZXC)ZXC,SUM(XYZ)XYZ

FROM DATABASENAME..WWW1

WHERE A1 BETWEEN '20091009' AND '20100408'

AND A2 = 130

AND A3 > 10000

GROUP BY A2, ABC

) a

GROUP BY A2, ABC

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

Bhuvnesh (4/24/2010)
Jeff Moden (4/23/2010)
Also, is there a chance that WWW and WWW1 are actually views.
Why do you think that it could be view ? because of "Select * " ?

No... I asked the question because many performance faults are caused by views, especially non-indexed aggregated views and most people simply forget about them. It's just one of those questions that needs to be asked in light of so little information by the OP.

--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)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Jeff Moden (4/24/2010)
Bhuvnesh (4/24/2010)
Jeff Moden (4/23/2010)
Also, is there a chance that WWW and WWW1 are actually views.
Why do you think that it could be view ? because of "Select * " ?
No... I asked the question because many performance faults are caused by views, especially non-indexed aggregated views and most people simply forget about them. It's just one of those questions that needs to be asked in light of so little information by the OP.

hmm got your point , can u suggest me any link where i can study about "view optimization stuff" , i know that behind the scene view are basically queries but still 😉

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)