Re: i Access IBMDASQL performance

Your problem sounds similar to issues I experienced.

I do quite a bit of extracts with MS SQL.

What you are describing is a problem using MS SQL. As an example, I have been doing extracts with millions of records. The tables have millions of records.

We found that MS SQL does not use the where clause when selecting the data from the IBMi. It copies the entire file from IBMi to the SQL server and then performs the where clause. This was pretty annoying as sometimes we only needed 100 records.

We used 2 technics to solve our problem.

- we used views on the IBMi to reduce the number of records transferred to the SQL database or that SQL could pull.
- due to the size of the files, we switch many of them to run as DB2 scripts directly on the IBMi.

We don't use stored procedures but stored procedures may also speed up the process using MS SQL.

Darryl.

Sent from my iPad

On 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.orthatjar 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.