Oracle linked server extremely slow.

[stephenk1973]

I have a linked Oracle server which takes me an absolute age to query even with the simplest queries.

I am getting no errors but one quirk that occurs is when the Oracle table is queried in Enterprise Manager, the query completes but automatically gives the queried table and alias....

Select * From ATEST..OWN.TABLE
becomes
Select * From ATEST..OWN.TABLE TABLE_1

I have been to the Microsoft site and it prompted to go through the Registry settings but still no joy.

Any help appreciated.Thanks

Stephen

 

[chrissie1]

did you try it via openquery(linkedservername,"selectstatment")

it should be faster but never good.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[stephenk1973]

When i try 'Openquery' i get....Server: Msg 156, Level 15, State 1, Line 1, not sure, syntax looks right any suggestions? Is the Link server not linked properly?

My concerns are because the simple queries are currently being out performed by MS Access running on my desktop.

The oracle db table had more than 6 million rows in it, Access responds in less than a second, SQL server gives me enough time and go make the coffee and break open the biscuits. I can think there must be something wrong in the server build or the configuration.

Will continue to try and get OpenQuery to work, but any other suggestions gratefully accepted.

Thanks

Stephen

 

[NoCoolHandle]

THe difference in speed is where the data presides and what the client is.

Access likes to Cache data. YOu link, open and bam it starts grabbing data and caching it locally. THe reason is that Access is a DESKTOP APPLICATION that pretends to be a database server.

SQL will never do that. And you should NEVER qusery a table with 30000000 records without a WHERE CLAUSE.

And use openquery should be your answer to slowish query if you add a where clause otherwise expect a delay as sql will still need to bring back all the records (networks arnt that fast).

WHAT you might want to do if you want select * performance is to use dts to do periodic imports of data or to replicate your oracle table to sqlserver. This would have the benifit of fast performance again (and be doing all the masive datamovements that access likes to do wihtout some of the locking.

Rob