linked server performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.
It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:

>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>