Re: i Access IBMDASQL performance

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Pretty sure Darryl hit it on the head.

With a linked server, if you simply query it directly (with a 4 part name)
select * from FALCONPROD.S10316ZM.HLTHPRDFIL.CUSTOMP where locnbr = '00335';

It pulls back the entire table and does the WHERE locally on the MS SQL
Server.

You have to using OPENQUERY to send the SQL statement itself to the remote
server and get back just the rows you are interested in.
select * from openquery(FALCONPROD, 'select * from customp where locnbr =
''00335''');

Charles


On Thu, Jul 2, 2015 at 5:25 PM, Justin Taylor <JUSTIN@xxxxxxxxxxxxx> wrote:

> I'm trying to set up a linked server in MS SQL Server using the IBM I
> Access for Windows IBMDASQL data access provider.  Easy queries are taking
> up to 30 minutes to complete.  I've been working with IBM support since
> last week.  We got one query down to 22 minutes.  Since the same query
> using the jt400.jar driver and a Java SQL client takes just over 1 second,
> I think there's still room for improvement.
>
> Has anyone done anything with this before?
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.