Union Issue

[atiqraza]

Could somebody comment on a better way of doing this. I have three tables...all three close to 2 Gigabytes each. and all three have the same structure.
It is taking alot of time to finish the query. Infact still not done at two hours.
I am selecting data from these three tables and doing a union of the results so my queries look like this.

Could someone tell me of a better way of doing this?

select Region_ID
,[Agency ID]
,Customer_Number
,Document_Number
,document_Date
,min(Price) From trans
Group by
,[Agency ID]
,Customer_Number
,Document_Number
,document_Date

UNION
select Region_ID
,[Agency ID]
,Customer_Number
,Document_Number
,document_Date
,min(Price) From trans
Group by
,[Agency ID]
,Customer_Number
,Document_Number
,document_Date

 

[AlexCuse]

If you are certain there are no duplicate rows between the tables, UNION ALL could be significantly faster. UNION checks for duplicates I believe.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[atiqraza]

Is it possible to Group by on the whole UNION ?

 

[SQLSister]

Alex is onthe right track when he mentions Union all. Another thing to consider is if you need to add a where clause to each select to get more specific records. This query as written given the size of the tables as described would probably return millions of records. Is that what you really want or do you want the records only within a specific date range for instance?

The group by must be done on the individual selects or you would have no way to get the min(price).

You could also consider inserting the results of each select into a temp table or table varaible. This might be faster or might not depending on the specifics of how busy your system is and how big your temp db is. But truly I think you need to rethink what data you want. This is returning more records than would be considered useful for most purposes.

Questions about posting. See faq183-874