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 transGroup by ,[Agency ID],Customer_Number,Document_Number,document_Date UNION select Region_ID,[Agency ID],Customer_Number,Document_Number,document_Date ,min(Price) From transGroup by ,[Agency ID],Customer_Number,Document_Number,document_Date |
|
X002548
Not Just a Number
15586 Posts |
|
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 |
|
|
X002548
Not Just a Number
15586 Posts |
|
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 |
|
|
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 |
|
|
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 thisselect 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 dGroup by Region_ID, [Agency ID], Customer_Number, Document_Number, document_Date Peter LarssonHelsingborg, Sweden |
|
|
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 |
|
|
X002548
Not Just a Number
15586 Posts |
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|