Avatar of Varshini
Varshini asked on

Is there is any alternatives for big union query in sql ?

Is there is any alternatives for big union query in sql ?
how do i split big union querys ?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Varshini

8/22/2022 - Mon
dhshah_in

The UNION query is quite efficient and works even on large tables. I have personally run it on tables as large as few hundred million records and it completes in a day or two max. But the important thing here is RAM. You need a good amount of RAM to speed up your query.

Suppose that is not an option, then below is the alternative:

The purpose of running a UNION query is to merge two tables and remove duplicates.
The fastest alternative is to simply insert the second table into the first table. I do this using the import/export wizard which is quite fast in inserting rows.

After the insert is over, we now need to remove duplicates.

For this I use the procedure provided by Microsoft in this wonderful step by step article: http://support.microsoft.com/kb/139444


Lowfatspread

it depends on what your union query is actually doing...

please post it....

or at least give further information on its structure...

e.g.  is it returning a result set for multiple criteria but against the same underlying tables...
  or is it returning a result set from multiple table sources?

what do you mean by big ...?
a large number of rows in the result set , or the query itself occupies many lines of code?
ASKER CERTIFIED SOLUTION
subhashpunia

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Varshini

hi Lowfatspread:

  it is not the result set of same tables its from different tables.
the union is like ....

select  busineess_id,date,sales_amount from (
select  busineess_id,date,sales_amount from  sales_southzone il where date='2010-11-11'
union
select  busineess_id,date,sales_amount from  sales_northzone  where date='2010-11-11'
union
select  busineess_id,date,sales_amount from  sales_eastzone  where date='2010-11-11'
union
select  busineess_id,date,sales_amount from  sales_westzone  where date='2010-11-11'
union
select  busineess_id,date,sales_amount from  sales_northeastzone  where date='2010-11-11'
union
select  busineess_id,date,sales_amount from  sales_southeastzone  where date='2010-11-11'
) from a where a.

--- each where clause has to many conditions and final outer query also has more condition and some query has included left out join

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
twol

If you place the queries in a stored procedure, it will allow you to insert the rows from the queries into a temp table. Then you can query the temp table with the final conditions and joins and return that as the result set. The stored procedure will allow for a lot of flexibility in how you build the temptable and could provide easier maintenance in the long run.
Lowfatspread

what is the reason for the underlying table structure?

do the tables exist in the same database?
could a partioned view be constructed over the tables?
ASKER
Varshini

the db has  created 5 years back. So i do not know the reason.   The tables exists in the same db.
I have no idea of partioned  views and in the query i have so many outer joins so i do not able to create indexed views.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.