Topic
  • 4 replies
  • Latest Post - ‏2012-07-28T18:50:39Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts

Pinned topic Distributed Queries: DB2 and SQL Server v.2008.r2

‏2012-07-25T23:38:14Z |
Techies--

I have an mssql linked server pointing to an iSeries 7, db2 6.1 data source. I have select statements which-- when I look at the query plan, are primarily taken up in remote processing. These are select statements with simple (text, integer based data) retrieval requirments on the surface. I initially had the remote collation flag on the linked server enabled to true. The queries took over a minute to return. When I disabled the feature, the queries returned in under 5 seconds, however more restrictive distributed queries are creeping back up to just under 8 seconds. Does anyone from the DB2 /iSeries world have any suggestions?

Kind regards,

Paula DiTallo
plditallo@ieee.org
Updated on 2012-07-28T18:50:39Z at 2012-07-28T18:50:39Z by SystemAdmin
  • albamain
    albamain
    3 Posts

    Re: Distributed Queries: DB2 and SQL Server v.2008.r2

    ‏2012-07-26T07:31:48Z  
    remote collation ON means the sort of text data is performed on server side, so it could be a slower process. OFF means the sort is performed on local side (i.e. by SQL Server).
    For a quick test, try to select just non-textual data: the remote collation should become meaningless
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Distributed Queries: DB2 and SQL Server v.2008.r2

    ‏2012-07-26T13:26:22Z  
    • albamain
    • ‏2012-07-26T07:31:48Z
    remote collation ON means the sort of text data is performed on server side, so it could be a slower process. OFF means the sort is performed on local side (i.e. by SQL Server).
    For a quick test, try to select just non-textual data: the remote collation should become meaningless
    Thanks for responding--I've tested this, and yes integers have a 3 second response time with remote collation on or off. So, the dissimilar collation does make a difference.

    The trouble with these distributed queries is that I have something that launches from the mssql side that looks like this with some frequency --

    SELECT x.col1, x.col2
    from LINKED.SERVER.ISERIES_DB2.TableName x
    where x.col1 in
    (
    select col1
    from http://local.mssql2008r2.TableName
    where col1 = somevalue
    union all
    select col1
    from http://anotherlocal.mssql2008r2.TableName
    where colx = somesimilarvalue

    .
    .

    );

    ... so I have statements where the filtering on the predicate clause doesn't appear to happen until after the cargo is returned back from the db2 remote server. I've suggested to some of the .net developers to try dumping what's in the subqueries into temp table values (or if a lot of values, temp tables) in an attempt to generate as many "where =" rather than "where in" phrases as possible when remoting over to the db2 data source.
    Kind regards,
    Paula DiTallo
    plditallo@ieee.org
  • albamain
    albamain
    3 Posts

    Re: Distributed Queries: DB2 and SQL Server v.2008.r2

    ‏2012-07-26T13:53:34Z  
    Thanks for responding--I've tested this, and yes integers have a 3 second response time with remote collation on or off. So, the dissimilar collation does make a difference.

    The trouble with these distributed queries is that I have something that launches from the mssql side that looks like this with some frequency --

    SELECT x.col1, x.col2
    from LINKED.SERVER.ISERIES_DB2.TableName x
    where x.col1 in
    (
    select col1
    from http://local.mssql2008r2.TableName
    where col1 = somevalue
    union all
    select col1
    from http://anotherlocal.mssql2008r2.TableName
    where colx = somesimilarvalue

    .
    .

    );

    ... so I have statements where the filtering on the predicate clause doesn't appear to happen until after the cargo is returned back from the db2 remote server. I've suggested to some of the .net developers to try dumping what's in the subqueries into temp table values (or if a lot of values, temp tables) in an attempt to generate as many "where =" rather than "where in" phrases as possible when remoting over to the db2 data source.
    Kind regards,
    Paula DiTallo
    plditallo@ieee.org
    Paula, you're right: the best is to dump filter conditions in temp table and pass the values to the main select. If possible, I'd suggest to avoid the 4-level syntax and try to use the OPENQUERY syntax instead, building the "actual" SELECT query at run-time (you can use sp_executesql)): the WHERE condition of the 4-level syntax is applied locally once the entire result set has been downloaded to the SQL Server side, while a condition sent via OPENQUERY is applied directly to the DB2 side. If table x is huge, it could be a big difference.
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Distributed Queries: DB2 and SQL Server v.2008.r2

    ‏2012-07-28T18:50:39Z  
    • albamain
    • ‏2012-07-26T13:53:34Z
    Paula, you're right: the best is to dump filter conditions in temp table and pass the values to the main select. If possible, I'd suggest to avoid the 4-level syntax and try to use the OPENQUERY syntax instead, building the "actual" SELECT query at run-time (you can use sp_executesql)): the WHERE condition of the 4-level syntax is applied locally once the entire result set has been downloaded to the SQL Server side, while a condition sent via OPENQUERY is applied directly to the DB2 side. If table x is huge, it could be a big difference.
    The battle for performance continues!:-)

    I've looked at improving performance from the mssql 2008/r2 to remote db2 perspective. Is it a better idea for DB2 v6.1/iSeries to link back to ms sql server? I suppose It really depends on which optimizer is better equipped to handle distributed queries. Any thoughts?

    Kind regards,
    Paula DiTallo
    plditallo@ieee.org