issues in Linked server to Informix IDS

I have used the ole db services for odbc to utilise a informix odbc driver t
o
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.Look up in SQL Server Books Online Linked Servers OLEDB Provider Properties.
If you misset these, then SQL Server will have to copy all data to a temp
table before applying any filters and/or rollup aggregations. Basically,
you want to offload as much work as possible to the data provider for which
you are extracting data.
To modify these properties, initiate the creation of a new linked server for
the same OLEDB provider type, then select the provider properties button.
After setting the properties, and saving, then you can cancel the creation
of that specific linked server.
Provider properties are set server-wide for linked servers of that provider
type.
Keep in mind, however, that if you ever join a table from a linked server
and the local server, there will have to be some sort of data copy into a
temp table in order to hash the join. There is no way around this as the
data must be gathered collectively to a single host to process. You want to
process as much pre-filtering on the invidual host prior to joining the
remaining result sets.
Sincerely,
Anthony Thomas
"CPiO" <CPiO@.discussions.microsoft.com> wrote in message
news:76C6DE2C-F066-41A3-9245-39B3742DB9B2@.microsoft.com...
I have used the ole db services for odbc to utilise a informix odbc driver
to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.|||Hi Anthony,
Thankyou for your reply, as suggested I have tried modding the properties
behind the ole provider for odbc but this has made no difference.
All that we are doing is creating a view inside a SQL dbase which is looking
at a table in the IDS system.
THEN
We are then firing a selective query to that view where we are seeing 3-4
mins of wait time. Again access into that table is fine via query and odbc.
Any more ideas would be greatly apreciated.
James
"CPiO" wrote:

> I have used the ole db services for odbc to utilise a informix odbc driver
to
> create my linked server in SQL server. The Informix IDS server in questio
n
> is v9.4 on Suse Linux Enterprise V8.
> If I use the informix ODBC via Query in Excel I can retrieve data from the
> IDS databases with no problem, it is very quick.
> If I create a view in SQL server (which uses the linked server) to look at
> the same data in the IDS databases the same data will take an avaerage of
> 50seconds to come back.
> I am lost, please advise.
> Could it be the way in which I have setup the linked server, msdtc, tempdb
> etc etc.