SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Union Problem		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

atiqraza
Starting Member

11 Posts
Posted - 02/06/2007 :  13:23:45  Show Profile  Reply with Quote
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 - 02/06/2007 :  13:25:38  Show Profile  Reply with Quote
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
Edited by - X002548 on 02/06/2007 13:26:58
Go to Top of Page

atiqraza
Starting Member

11 Posts
Posted - 02/06/2007 :  13:28:25  Show Profile  Reply with Quote
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 - 02/06/2007 :  13:41:50  Show Profile  Reply with Quote
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

USA
35007 Posts
Posted - 02/06/2007 :  13:45:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2871 Posts
Posted - 02/06/2007 :  14:01:14  Show Profile  Visit jezemine's Homepage  Reply with Quote
why not do a cross join on all these tables? ;)

www.elsasoft.org
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 02/06/2007 :  14:02:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
35007 Posts
Posted - 02/06/2007 :  14:02:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 02/06/2007 :  14:03:11  Show Profile  Reply with Quote
[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

Slovenia
11741 Posts
Posted - 02/07/2007 :  04:39:46  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000