January 9, 2006 at 3:33 pm
We have a new 2005 database that I have linked to an Oracle 9i database. I have tried using both the Microsoft OLE and the Oracle OLE database drivers for the linking; however, any queries we run against it are EXTREMELY slow. It took over 2 minutes to pull back 1 row from a table that should have been using an index... select * from invfile where in_invnum = 2, and there is a primary key index on in_invnum. When I looked at it from the Oracle perspective while it was running, it wasn't using indexes at all, it was doing a weird full-table scan.
Does anyone have any ideas about what could be wrong? Thanks...
January 10, 2006 at 5:07 pm
Have you tried it in the 2k enviroment? There could be reserved characters. Beside that I do not know. In 2K I am not having that trouble it works fairly quickly. 100K rows in 2 minutes. I have not done it in 2K5 so I will have to see
January 11, 2006 at 5:00 am
Hi Amy,
I'm running 8i, so I don't know how relevant what I know is.....
The way we run our Oracle procedures is via linked servers - I link the server into SQL (you have to run the caths.sql script to enable HSODBC on Oracle) and then query direct to the linked server, this works extremely well, in fact it is easier to get a connection to Oracle this way than MS SQL 2000 through 2005. There are some syntax issues which are a pain for a couple of days.
The only other half pence worth of information I have is that I've never tried to run OLEDB without an Oracle client installed on the SQL server, can't remember why, the reasons for what I did and why I did it are lost in the mists of time.....
What else? Oh yeah - a query can fail horribly for various reasons and break your connection - because the query is coming from SQLServer.exe the Oracle server never closes this session, it keeps it open on the server, this has the effect of cluttering up the pipe in some unknown way. Even if you kill the connection on the Oracle server it keeps the thing alive as a "pseudo" connection. Don't know the ins and outs, we never managed to work it out, but the hack answer is to kill the session on Oracle and then reboot the SQL server, then make sure your query syntax is good. We had a fair few reboots at first but now our queries are right it doesn't happen.
One other little snippet which is probably the most useful thing I've learned, Oracle Minus queries are (for me at least) BLINDING fast, absolutely fantastic for synching Oracle --> SQL.
January 11, 2006 at 11:25 am
Hmmm, that's really helpful Richard... thanks! But are you linking FROM Oracle TO a SQL Server? Am I understanding that correctly? How do you do that, can you give me an example of the syntax? I thought you had to buy a SQL Server Gateway product in order to do that...
Thanks!
Amy
January 11, 2006 at 3:10 pm
Hi Amy,
I had troubles with speed from oracle linked servers. I found that if I went into the 'Linked Server Properties' for the linked server and modified the 'Server Options' - tick all the boxes - and it sped up no end. The options that are ticked on my connection are: Collation Compatible, Data Access, RPC, RPC out, and Use Remote Collation. Not all of these are ticked by default.
I hope that this makes sense and works for you. If you want I can include a screen dump for you..
Enjoy
Michael
January 12, 2006 at 5:35 am
Just a thought - I've got some good internal documentation for setting up HSODBC from Oracle 8i --> MS SQL and vice versa, if anyone wants it you're welcome to it, just send me a private message with your email address.
May 25, 2006 at 7:27 am
I really need this. Please send to amyxie@msn.com.
Now we have a big trouble with sync data from Oracle to Sqlserver
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply