We have the following select statement:
SELECT gi.anbr AS A_Number, gi.Adt AS A_Date, ct.Atypnm AS A_iSSUE,
gi.Amedt AS A_ISSUE_Date, gi.Atypcd AS A_CODE
FROM OPENQUERY(DB2Link, 'SELECT * FROM Sch.GenInfo') AS gi
LEFT OUTER JOIN OPENQUERY(DB2Link, 'SELECT * FROM Sch.AtypcdTABLE') AS ct
ON gi.Atypcd = ct.Atypcd;
GO
This is running off sql server 2005 with the openquery part on a DB2 database.
Without making any changes to the db2 database (creating a view/table there
with all the joins already done), can you provide some ideas on how to better
write this query? Tried putting the column names in where the * was but that
only saved 2 minutes in time.

Well first and foremost for any query is don't use SELECT *. Instead only
call out the columns you actually need. And why don't you have a WHERE
clause? Is the join condition really the determining filter? But the real
kicker here is that you are making two distinct remote calls, returning the
entire tables locally and then joining them. When you join to a remote table
SQL Server usually can't optimize it the way it can for local tables. So you
almost always get the remote table copied locally and then joined and
filtered which is a lot of work. In your case why don't you simply do the
join in one OpenQuery call since they are both on the same schema of DB2?

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Tracey" <Tracey@discussions.microsoft.com> wrote in message
news:741CAA60-3156-4E09-BE2A-11278519C0D8@microsoft.com...
> We have the following select statement:
> SELECT gi.anbr AS A_Number, gi.Adt AS A_Date, ct.Atypnm AS A_iSSUE,
> gi.Amedt AS A_ISSUE_Date, gi.Atypcd AS A_CODE
> FROM OPENQUERY(DB2Link, 'SELECT * FROM Sch.GenInfo') AS gi
> LEFT OUTER JOIN OPENQUERY(DB2Link, 'SELECT * FROM Sch.AtypcdTABLE') AS
> ct
> ON gi.Atypcd = ct.Atypcd;
> GO
> This is running off sql server 2005 with the openquery part on a DB2
> database.
> Without making any changes to the db2 database (creating a view/table
> there
> with all the joins already done), can you provide some ideas on how to
> better
> write this query? Tried putting the column names in where the * was but
> that
> only saved 2 minutes in time.
>

I did just pull the columns as well and it saved 2 minutes query time. I
will do that for sure. Just trying to find the real timesaver and thing it
will be the joining the 2 db2 tables inside the openquery you mentioned.
However, I am unfamiliar on how to do the join of two db2 tables. All the
examples i have seen are selecting from one table. I will definitely google
and see if i can find examples of where the tables are joined in the
OPENQuery section. Dont you have to do everything in the OPENQuery parens ()
in DB2 lingo?
Thank you for pointing me in the right direction. If you happen to know how
to do the join referencing db2 tables...;-)
Thank you again

"Andrew J. Kelly" wrote:

> Well first and foremost for any query is don't use SELECT *. Instead only
> call out the columns you actually need. And why don't you have a WHERE
> clause? Is the join condition really the determining filter? But the real
> kicker here is that you are making two distinct remote calls, returning the
> entire tables locally and then joining them. When you join to a remote table
> SQL Server usually can't optimize it the way it can for local tables. So you
> almost always get the remote table copied locally and then joined and
> filtered which is a lot of work. In your case why don't you simply do the
> join in one OpenQuery call since they are both on the same schema of DB2?
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Tracey" <Tracey@discussions.microsoft.com> wrote in message
> news:741CAA60-3156-4E09-BE2A-11278519C0D8@microsoft.com...
> > We have the following select statement:
> > SELECT gi.anbr AS A_Number, gi.Adt AS A_Date, ct.Atypnm AS A_iSSUE,
> > gi.Amedt AS A_ISSUE_Date, gi.Atypcd AS A_CODE
> > FROM OPENQUERY(DB2Link, 'SELECT * FROM Sch.GenInfo') AS gi
> > LEFT OUTER JOIN OPENQUERY(DB2Link, 'SELECT * FROM Sch.AtypcdTABLE') AS
> > ct
> > ON gi.Atypcd = ct.Atypcd;
> > GO
> > This is running off sql server 2005 with the openquery part on a DB2
> > database.
> > Without making any changes to the db2 database (creating a view/table
> > there
> > with all the joins already done), can you provide some ideas on how to
> > better
> > write this query? Tried putting the column names in where the * was but
> > that
> > only saved 2 minutes in time.
> >
>
>

This is just a WAG, but are those two tables very large? If I were a
betting man I would say that the each individual query is pulling every row
of each table across to your SQL Server where the join is being performed on
them. What if you make the DB2 server perform the join instead? Maybe
something like this:

SELECT x.anbr AS A_Number, x.Adt AS A_Date, x.Atypnm AS A_iSSUE,
x.Amedt AS A_ISSUE_Date, x.Atypcd AS A_CODE
FROM OPENQUERY(DB2Link, 'SELECT gi.anbr, gi.Adt, ct.Atypnm,
gi.Amedt, gi.Atypcd
FROM Sch.GenInfo AS gi
LEFT OUTER JOIN Sch.AtypcdTABLE AS ct
ON gi.Atypcd = ct.Atypcd;') AS x;

Note that this is untested, and I'm unfamiliar with the peculiarities of
DB2, but it may be worth a try.

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Pro.../dp/143021001X


"Tracey" <Tracey@discussions.microsoft.com> wrote in message
news:741CAA60-3156-4E09-BE2A-11278519C0D8@microsoft.com...
> We have the following select statement:
> SELECT gi.anbr AS A_Number, gi.Adt AS A_Date, ct.Atypnm AS A_iSSUE,
> gi.Amedt AS A_ISSUE_Date, gi.Atypcd AS A_CODE
> FROM OPENQUERY(DB2Link, 'SELECT * FROM Sch.GenInfo') AS gi
> LEFT OUTER JOIN OPENQUERY(DB2Link, 'SELECT * FROM Sch.AtypcdTABLE') AS
> ct
> ON gi.Atypcd = ct.Atypcd;
> GO
> This is running off sql server 2005 with the openquery part on a DB2
> database.
> Without making any changes to the db2 database (creating a view/table
> there
> with all the joins already done), can you provide some ideas on how to
> better
> write this query? Tried putting the column names in where the * was but
> that
> only saved 2 minutes in time.
>