SQL performance issue local remote database-CodePudding

I'm trying to select several 1000 rows from a remote database (where I can change nothing). I have (string) IDs to filter the needed data rows but I'm having performance issues.

Using a simple sql select I can retrieve the data in ~4 s. `

SELECT
    myid,
    column2
FROM
    view1@remotedb
WHERE
    myid IN ( '1', '2', '3' )

Since I need to select several thousand rows I'm using a local database for the IDs to select. `

SELECT /* DRIVING_SITE(V1)*/ myid,
    column2
FROM
    view1@remotedb    v1,
    localdb           t1
WHERE
    v1.myid = t1.myid;

` Unfortunately even for only 3 IDs in t1 the execution time increases to 3 min. Using driving_site or not makes no difference. Is there a way to increase the performance?

CodePudding user response：

Please check if keeping the localdb in the subquery helps.

SELECT
    myid,
    column2
FROM
    view1@remotedb
WHERE
    myid IN ( SELECT myid from localdb)

CodePudding user response：

In this thread on this same problem I found this little helpful bit:

Hints require a character after the " ".

You don't have a space between the and DRIVING_SITE.

I am super confused by this thread as it seems people are claiming this is necessary as well as that it isn't (but in the end OP says it helped). But it's worth trying. Please check the execution plan to check whether it looks like the hint is honored. What you want to achieve is that t1 is sent to the remote db and the join performed there instead of v1 getting sent to your local db and joined there.

You find the documentation for the syntax here.