October 3, 2007 at 12:57 pm
I've inherited a DTS package from another analyst. Data in one Oracle 10g database is combined w/ data from another 10g db, then loaded to SQL Server 2000 for final processing and storage.
What the current process does with 3 Million + records is:
(1) load records from first Oracle db into a staging table on SQL server, then (2) copy this to a .txt file for use by sql loader (on a local machine running the package, we do not have file access to our SQL server). (3) This data is loaded to the second Oracle db with sql loader running in a cmd shell invoked by the dts, merged with the data needed, then (4) loaded BACK to the SQL server for final processing and storage of the resulting dataset.
Currently, the total combined process takes upwards of 5-6 hours to complete during high traffic times. Now I know that using DTS for Oracle-Oracle transactions is slow (even when not using the MS ODBC driver for Oracle), hence the original choice to use a sql loader step, but wouldn't this be negligible given the time this already takes to complete, and also considering the added benefit of being able to schedule the thing server-side by eliminating the sqlldr step???
Considerations:
- Neither of the oracle dbs are linked, so I can't run a query on one to pull from the other. This is a decision made over our heads.
- We don't have file access to our sql server, so any sql loader task has to take place on a client machine.
I would appreciate anyone's thoughts on this. I desperately need a better way!!!
Thanks!
Luke
MIS Analyst
October 4, 2007 at 4:21 am
Which of the steps takes the longest time?
As an alternative, why not download the required data from the second Oracle db into SQL Server and do the merge processing in SQL Server than Oracle?
Jez
October 5, 2007 at 5:13 am
You state that 'using DTS for Oracle-Oracle transactions is slow' but I've not necessarily found this to be true, especially if the oracle databases are on separate servers.
It's worth looking at the way the file is accessed from DTS - if you can change the extract statement to use openquery this may help, also check your not extacting more than your actually using (i.e. is this select * )
I agree with looking at doing two extracts into SQL and merging there rather than on Oracle, but it would make sense to get some timings on each step first.
October 5, 2007 at 8:18 am
I'd love to load both to SQL Server, but only the barebones-necessary data from both db's needed for the merge is estimated at over 4 GB, and I don't have enough space on my db for that kind of transaction...
juliekenny, your suggestion to use openquery sounds intriguing... can you give me more information on that?
Thanks!
October 5, 2007 at 8:31 am
Briefly,
1. Set up a linked server connection for each Oracle database.
2. In the DTS transformation
set the source connection to the SQL local database
set the source type to 'SQL Query'
write an openquery statement to read the data via the linked server e.g.
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
For details on how to use openquery see Books Online - it explains better than me.
If you do this the SQL query will be run on the Oracle server and can be more efficiant.
Another way may be to have a procedure on the oracle database, keep the DTS link as the Oracle server but run the procedure - I've never done this as I'm not Oracle and am not in a position to ever change the source databases, but I guess it should do the same.
October 5, 2007 at 9:09 am
well, nevermind on the openquery, our sql server does not have rights on either oracle server. That would be a phenominal amount of red tape, unless there is a way I can pass my login info...
October 5, 2007 at 9:12 am
It's never easy is it - or you wouldn't have posted I guess. How does step 1 work currently if SQL can't access Oracle, I must have misunderstood.
October 5, 2007 at 9:19 am
I just transform between an MS OLE DB for oracle connection and a conn for the sql server... Now that I think about it, it seems that I could leave this step out and just transform directly to the txt file to save a 45min step... the real bottleneck is getting the data back into the second oracle, even the sql loader task takes about 3 hrs at this.
I guess there is just no better way than what we're doing now.
October 5, 2007 at 9:23 am
That makes sense, however I would have thought the access rights for this would not be different to those required for openquery - in both cases your looking for a read only access to the database and should be able to use exactly the same credentials.
October 5, 2007 at 9:35 am
Ok, I'm dumb... My login on the sql server is different from that on the oracle server. different id naming conventions for different departments... love this company.
It doesn't look as though I've got any other solution at this point except just to further tune this as is...
October 5, 2007 at 9:46 am
Ah,
sorry, don't think what I said was what I actually meant there. The idea was that if you have a login to Oracle which can read the data that is the login you give to the linked server.
However this is only a maybe on speed up. Putting it direct to text sounds like a simpler approach - and simplier is nearly always better.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply