Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Union Problem

Author  Topic 

atiqraza
Starting Member

11 Posts

Posted - 2007-02-06 : 13:23:45
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



X002548
Not Just a Number

15586 Posts

Posted - 2007-02-06 : 13:25:38
Where's the where clause?

Also you GROUP BY on the entire union, not each part, unless it's in a derived table





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

atiqraza
Starting Member

11 Posts

Posted - 2007-02-06 : 13:28:25
I am selecting the whole table for the union, I have alrady deleted all of unnecesry Data
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-06 : 13:41:50
hmmmmmm...what do you plan to do with 6 GB of data?

And I only see 2 tables

And are you expecting duplicates and that's why you are using UNION?

What are you trying to accomplish, besdies bringing down the server




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-06 : 13:45:37
Oh my!!!

You probably don't even have enough memory on the client to receive that much data.

Tara Kizer
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-06 : 14:01:14
why not do a cross join on all these tables? ;)


www.elsasoft.org
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 14:02:18
The logic is not correct. What if price is 2 from trans1 table, and price is 3 from trans2 table? Then the UNION will get both records.

I think you need something like this
select		Region_ID,
[Agency ID],
Customer_Number,
Document_Number,
document_Date,
min(Price)
From (
select Region_ID,
[Agency ID],
Customer_Number,
Document_Number,
document_Date,
Price
from trans1
union all
select Region_ID,
[Agency ID],
Customer_Number,
Document_Number,
document_Date,
Price
from trans2
) as d
Group by Region_ID,
[Agency ID],
Customer_Number,
Document_Number,
document_Date




Peter Larsson
Helsingborg, Sweden
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-06 : 14:02:21
quote:
Originally posted by jezemine

why not do a cross join on all these tables? ;)





Tara Kizer
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-06 : 14:03:11
[chuckle]

quote:
Originally posted by jezemine

why not do a cross join on all these tables? ;)


www.elsasoft.org



[/chuckle]




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-07 : 04:39:46
i hope you have a good clustered index.
this might help:
http://weblogs.sqlteam.com/mladenp/articles/9502.aspx



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
   

- Advertisement -