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.
| Author |
Topic |
|
Tigger
Yak Posting Veteran
85 Posts |
 Posted - 2002-01-17 : 23:19:02
|
Thought this might be of use to someone.I have 3 Oracle tables each containing over 1 million records and I was asked to download some data from these tables into a table on SQL2000. Most of the data was from tables A and B with one field required from table C. The query would download about 100 of those records.My first attempt was to write a SQL query joining all 3 tables and downloading the required fields. SELECT A.CLAIM_NO, A.SHORT_DESC, A.DESCRIPTION, A.LOSS_DATE, A.HANDLING_BRANCH_UNIT, A.BR_UNIT_POST, A.REPORTED_DATE, B.RISK_TYPE_ID, B.RESERVE, A.CUSTOMER_NO, C.ACCOUNT_NAMEFROM A, B, CWHERE B.RESERVE>=22500AND A.CLAIM_NO = B.CLAIM_NO AND A.CUSTOMER_NO = C.CUSTOMER_NOI ran the job and cancelled it after it had been running for nearly 2 hours !  Next attempt was to join tables A and B and download the required records and at the same time to download the required field + join field from table C into another table (Table D). Once the downloads were finished I then had an update query to add in the data from table D.This way it took about 10 minutes, which I thought was pretty good..Then I decided to try the LookUps section of the DTS download using Table_C as a LookUp table and then use the ActiveX part of the download script to lookup the field I needed. DTSDestination("ACCOUNT_NAME") = DTSLookups("Table_C").Execute(DTSSource("CUSTOMER_NO"))By doing this my query is now down to 18 seconds !  |
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-01-18 : 14:27:02
|
| I'm sure your first query did take a long time to run it's called a cartesian product. I would go back and double check your records if your second query is anything like the first. I don't mean to sound brash or rude but I'd be really concerned about your data. If you could I would suggest having a view created on the Oracle side and bringing that in. JamesH |
 |
|
|
|
|
|
|
|
LookUp tables in DTS