SLow Perfomance from select query . from mssql to dblink to oracle

Question

SLow Perfomance from select query . from mssql to dblink to oracle

liorvikel

SSChasing Mays

Points: 651
More actions
December 20, 2016 at 5:31 am

#332906

i have db link to oracle from mmsql
when i run localy in oracle query of select i get 4 sec result
from mssql to oracle i get 24 sec
can in increase this?
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

liorvikel (12/20/2016)
i have db link to oracle from mmsql
when i run localy in oracle query of select i get 4 sec result
from mssql to oracle i get 24 sec
can in increase this?
thanks

Is the query on the remote SQL Server selecting from a single table?

😎

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

The best way to improve performance for a linked server is to push as much of the query processing as possible on to the remote server. This is best accomplished by using OPENQUERY. However, it's only going to help if you have filtering criteria that can be applied to the remote side of the query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

kevaburg SSCoach Points: 18131 More actions · Answer 3

ODBC or OLE-DB?

Have you also created a linked Server from Oracle to SQL Server?

Where do you write the query that you execute?

A good practice here is to create a stored procedure on the server where the data is. On the other server all you Need to do is call the procedure using the database link / linked server. That will bring performance gains for certain.