Showing posts with label figure. Show all posts
Showing posts with label figure. Show all posts

Wednesday, March 7, 2012

Best Oracle source provider?

Hello,
I am using a massive Oracle db in SSIS and am trying to figure out the best way to increase performance in moving data from this db to sql server. As far as Oracle sources, which seem to have the best performance?
Thanks,
AdrianWhat did you mean by the 'Oracle sources'? We tried using OLEDB for oracle and the performance is really poor.|||I've had that experience as well with OLEDB. What I meant by sources is either the .NET OracleClient, Microsoft OLEDB provider for Oracle, or Oracle provider for OLEDB. I believe these are the only 3 options to connect to Oracle. I can't even use the Oracle provider for OLEDB without numerous errors, and have found that the other 2 are quite slow as well. So I was just wondering what other people's experiences were, and if they found 1 to be superior over another. And maybe some input from the SSIS developers as to what their suggestions are.
Thanks,
Adrian|||The OLEDB providers for Oracle that are currently available aren't terribly fast. Have you considered replicating (a subset of) your data to SQL Server from Oracle before (or at the beginning of) your package execution? This topic in BOL is probably where to start: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/2e013259-0022-4897-a08d-5f8deb880fa8.htm|||Hello,

We have built a super fast connector for Oracle for SSIS. We have been able to achieve about 30 times performance improvements over the OLEDB Oracle provider. E.g 1 million rows, we loaded in about 2:20 mins. Let me know if you are interested in beta bits.

Regards
Sidharth
http://www.persistentsys.com
siddossy@.hotmail.com

Best Oracle source provider?

Hello,
I am using a massive Oracle db in SSIS and am trying to figure out the best way to increase performance in moving data from this db to sql server. As far as Oracle sources, which seem to have the best performance?
Thanks,
Adrian
What did you mean by the 'Oracle sources'? We tried using OLEDB for oracle and the performance is really poor.|||I've had that experience as well with OLEDB. What I meant by sources is either the .NET OracleClient, Microsoft OLEDB provider for Oracle, or Oracle provider for OLEDB. I believe these are the only 3 options to connect to Oracle. I can't even use the Oracle provider for OLEDB without numerous errors, and have found that the other 2 are quite slow as well. So I was just wondering what other people's experiences were, and if they found 1 to be superior over another. And maybe some input from the SSIS developers as to what their suggestions are.
Thanks,
Adrian
|||The OLEDB providers for Oracle that are currently available aren't terribly fast. Have you considered replicating (a subset of) your data to SQL Server from Oracle before (or at the beginning of) your package execution? This topic in BOL is probably where to start: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/2e013259-0022-4897-a08d-5f8deb880fa8.htm|||Hello,

We have built a super fast connector for Oracle for SSIS. We have been able to achieve about 30 times performance improvements over the OLEDB Oracle provider. E.g 1 million rows, we loaded in about 2:20 mins. Let me know if you are interested in beta bits.

Regards
Sidharth
http://www.persistentsys.com
siddossy@.hotmail.com

Monday, February 13, 2012

Beginners Question: How to write this query

Hi -

I feel stupied b/c I can't figure this out but I hope it's just a few seconds for some of the more senior posters of this forum:

I have a table that looks like this:

Table:Request
ID: bigint
... (a bunch of table specific columns)
User_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)
Provider_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)

and then there is a second table that looks like this:

Table: Status
ID: bigint
RequestID: bigint (the request ID, so I can see all status messages for the request)
Time: Timestamp
Code: byte (there are only a few status messages)

The status table holds both the status for users and providers, as they use exactly the same status code. What I'd like to get is a table/view that looks like this:

RequestID:bigint (the request that this status belongs to)
User_Code: byte (the code of th most recent user status)
Provider_Code: byte (the code of the most recent provider status)

Somehow, I am blanking how to create a query that returns both user and provider codes in one row. Any help greatly appreciated!!!!

Oliver

Why has the Status table the column RequestID?

Otherwise the SELECT you want is along the lines of

SELECT RequestID, U.Code, P.Code FROM Request, Status U, Status P WHERE Request.User_Status_Id = U.Id AND Request.Provider_Code = P.Id

|||

Thanks - that improves my life. I had used multiple views before and it was getting messy ...