Linked Server Performance

Question

Post reply

Linked Server Performance

VA123

SSCarpal Tunnel

Points: 4272
More actions
September 21, 2007 at 7:48 am

#121709

I have a select query that I run in MS Access against a linked table on an AS400 machine that completes in several seconds. The same query run in SQL Server 2005 using a linked server to the AS400 takes approximately 1 minute to complete.
Both queries use the same system DSN.
The DSN uses the 'iSeries Access ODBC driver'.
The linked server on SQL is set as follows:
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: MSDASQL
We use SQL Server 2005 Standard Edition.
Does anyone have any advice on what I might try to improve performance?
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

How are you writing your query to run on SQL? Have you tried Openquery to see if it behaves more as expected?

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 2

Thanks, Antares.

No, I haven't tried Openquery.

I'm actually selecting against a view. I'm using Visual Studios to create a report model for Report Builder.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

My thought is most of the work actually is happening on SQL Server and not the AS400 machine because of the way the transaction is handled. Openquery will submit to the AS400 data provider and ensure is executing on the AS400 machine.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

You actually expect an Access ODBC driver to operate as fast as an SQL Server OLE DB driver when used against SQL SERVER? Use the proper driver to make a new DSN.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 5

Thanks for your reply, Jeff.

Unless I misunderstood your reply, it is the query in SQL Server running against the AS400 linked server that is slow.

Can you suggest a different driver for the system DSN? Can you suggest a different provider and product name for the linked server?

Thanks.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

No... sorry, I can't. I've never had to connect SQL Server to an AS 400...

Lookup "DTS Driver Support for Heterogeneous Data Types" in Books Online... I believe these would be the same drivers you need. If that doesn't do it for you, search Google for how to connect SQL Server to an AS 400 using a linked server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

John te Lintelo Say Hey Kid Points: 702 More actions · Answer 7

I would try using the IBM DB2 UDB for iSeries IBMDASQL OLD DB Provider.

Check out Optimizing Distributed Queries for details of what queries are passed through to the remote server. Also the execution plan will show what queries are running on the local and remote servers. You might find that som filtering is being performed locally, so a larger than expexted resultset is being returned from the remote server.

Using openquery will force the entire query to be executed on the remote server.

Key DBA SSCertifiable Points: 6029 More actions · Answer 8

Patrick,

We are using:

Name: iSeries Access ODBC Driver

Version: 10.00.06.00

Company: IBM Corporation

File: CWBODBC.DLl

Date: 6/6/2005

Using that iSeries Access ODBC Driver to configure a System DSN, on our SQL Server machine.

We then have a Linked Server created within SQL Server to the DB2 database.

Option 1 of 2:

We can query using either 4 part names

SELECT <column listing>

FROM <Linked Server Name>.<DB2 Database Name>.<ODBC DSN NAME>.<Table Name>

WHERE <column = 'value'>

... which error will out on ANY table that has one or more date/time/datetime field on the DB2 side.

Option 2 of 2:

Using Open Query, the statement is passed through the ODBC "tunnel" and the statement between the beginning ' and the ending ' is run against the DB2 SQL "engine".

Your SQL statement will need to be written in DB2 SQL syntax, NOT SQL Server syntax.

SELECT * FROM OPENQUERY

(<Linked Server Name>,

'

')

GO

Hope this helps.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 9

VA123

SSCarpal Tunnel

Points: 4272

September 25, 2007 at 5:54 pm

#736185

John and Damon,

Thank you very much for your help.

thierry Old Hand Points: 301 More actions · Answer 10

Hi,

I'm using "Microsoft OLE DB provider for DB2" driver, shipped with HIS and suitable for SQL Server Enterprise Edition.

It works very well, I used the "Data Access Tool" to configure a connection string that I enter in the linked server section of SQL Server Management Studio.

This driver is about 4 times faster than IBM Client Access driver, I can load about 25 millions lines per hour from AS400 to SQL Server.

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 11

Thanks, thierry.

I'm using the standard edition of SQL Server.

I will look into using the driver you use.

gmelhaff@comcast.net Mr or Mrs. 500 Points: 564 More actions · Answer 12

FYI - I had massive performance issue using oledb connection to DB2 using linked server with sql written inline sqlserver - I switched to using openquery and query went from nearly an hour to 2/3 seconds for same query.