?
Solved

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

Posted on 2010-11-13
7
Medium Priority
?
810 Views
Last Modified: 2012-05-10
Is there is any alternatives for big union query in sql ?
how do i split big union querys ?
0
Comment
Question by:Varshini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Expert Comment

by:dhshah_in
ID: 34127369
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


0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34133148
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?
0
 
LVL 6

Accepted Solution

by:
subhashpunia earned 2000 total points
ID: 34134907
Use a temporary table to insert all data from all queries that are in UNION one by one. This way you would be able to process queries one at a time with smaller transactions. It would also help in making the process better understandable.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Varshini
ID: 34144016
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

0
 
LVL 7

Expert Comment

by:twol
ID: 34170085
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.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34179632
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?
0
 

Author Comment

by:Varshini
ID: 34205682
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.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question