Re: Bad performance on Linked Server

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Hmm... I recognize those examples... :)

And just to clarify...

"openquery" is SQL Server's implementation of "SQL pass-through"

Finally note that it can be used for SELECT queries or INSERT/DELETE.

Charles



On Wed, Feb 24, 2021 at 7:10 AM Rob Berendt <rob@xxxxxxxxx> wrote:

> I think openquery may be the answer.  I have a monthly reminder to beat me
> up side the head with a 2x2 to remind me of this:
>
>
> <snip>
> openquery is just MS SQL Server's technique for a "linked server".  Some
> sql engines always do it.  Some do it within setup or connection
> configuration.
>
> openquery:
> I assume you're trying to set up a linked server?
>
> https://www-01.ibm.com/support/docview.wss?uid=nas8N1014514
>
> Once you get connected, note the following:
>
> -- Pull all rows from the table(s) back to MS SQL server and do the where
> locally
> select * from LINKEDSVR.MYIBMI.MYLIB.MYTBL where locnbr = '00335';
>
> -- Sends the statement to linked server for processing
> select * from openquery(LINKEDSVR, 'select * from MYTBL where locnbr =
> ''00335''');
>
> --OPENQUERY() isn't just for SELECTS
> delete from openquery(LINKEDSVR, 'select * from MYTBL where locnbr =
> ''00335''');
> </snip>
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  7310 Innovation Blvd, Suite 104
>           Ft. Wayne, IN 46818
> Ship to:  7310 Innovation Blvd, Dock 9C
>           Ft. Wayne, IN 46818
> http://www.dekko.com
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Justin Taylor
> Sent: Wednesday, February 24, 2021 8:59 AM
> To: MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: Bad performance on Linked Server
>
> CAUTION: This email originated from outside of the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe.
>
>
> I have a local SQL Server instance running on my PC with a Linked Server
> connected to my IBMi.  Running a simple query (select column_list_here from
> SCHEMA.TABLE) takes about 9x longer than SQuirreL (using JT400.jar) on my
> PC.
>
> Any suggestions on shrinking that performance gap?
>
> TIA
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com