linked view in access don't use index

Hello,
I have a table with two field (name of the table : table_with_two_field)
field1, field2
when i create a simple view with no clause where:
create view dbo.view_on_field WITH VIEW_METADATA
as
select field1 from dbo.table_with_two_field
and i linked this wiew on access 2000 and i join with a local table on
field1 i have not response (field1 is a primary key on local and field1
have an index on the sql server table table_with_two_field)
in the trace i can see sql server send all the data : "select field1 from
dbo.view_one_field"
but when i do the same with local table and attached table i have a reponse
directly
can you help me
I'm not sure what precisely you are trying to do, but any time you
fetch data from the server and perform a join to a Access/Jet table,
ALL of the data is fetched from the server, and the join performed
locally by Jet. I am assuming that since you are performing a
heterogeneous join that you are not intending to update the data. What
I would suggest is to select from the SQLS table or view using a WHERE
clause to limit the result set, and dump it into a local Access/Jet
table, which should be very fast since you have fetched the data
locally and eliminated the heterogeneous join. The same local table
can be reused multiple times by deleting all of the rows before
fetching new ones.
--Mary
On Mon, 3 Oct 2005 01:17:01 -0700, "toni"
<toni@.discussions.microsoft.com> wrote:

> Hello,
> I have a table with two field (name of the table : table_with_two_field)
> field1, field2
> when i create a simple view with no clause where:
>create view dbo.view_on_field WITH VIEW_METADATA
>as
> select field1 from dbo.table_with_two_field
>and i linked this wiew on access 2000 and i join with a local table on
>field1 i have not response (field1 is a primary key on local and field1
>have an index on the sql server table table_with_two_field)
>in the trace i can see sql server send all the data : "select field1 from
>dbo.view_one_field"
>but when i do the same with local table and attached table i have a reponse
>directly
>can you help me
|||Hello Mary,
I try to explain my problem, sorry for my english because i'm spain
when i use directly a linked table with a local table, i have data
immediatly, when I
see the trace, Access send every value field (use in join) from data from
the local table to the sqlserver.
The profiler trace send :
declare @.P1 int
set @.P1=2
exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT "field1" FROM
"dbo"."table1" WHERE ("field1" = @.P1)', N'925000029010202145'
select @.P1
exec sp_execute 2, N'925000029010202146'
exec sp_execute 2, N'925000029010202146'
exec sp_execute 2, N'925000029010202146'
Local table have 3 records
when i do a simple view like this
create view dbo.view as select field1 from dbo.table1
when i use directly this linked view with a local table,
the trace send :
SQL:BatchCompletedSELECT field1 FROM "dbo"."view " Microsoft? Access etc..
and slqserver send millions records to access ...
Why the linked view and the linked table not have the same reaction ?
I can't do a view with more filter because every user have his owner local
table.
Thank you very much for your help.
I can
Rega1
"Mary Chipman [MSFT]" wrote:

> I'm not sure what precisely you are trying to do, but any time you
> fetch data from the server and perform a join to a Access/Jet table,
> ALL of the data is fetched from the server, and the join performed
> locally by Jet. I am assuming that since you are performing a
> heterogeneous join that you are not intending to update the data. What
> I would suggest is to select from the SQLS table or view using a WHERE
> clause to limit the result set, and dump it into a local Access/Jet
> table, which should be very fast since you have fetched the data
> locally and eliminated the heterogeneous join. The same local table
> can be reused multiple times by deleting all of the rows before
> fetching new ones.
> --Mary
> On Mon, 3 Oct 2005 01:17:01 -0700, "toni"
> <toni@.discussions.microsoft.com> wrote:
>
|||I'm hazarding a guess here, but I suspect the reason why is because
Access cannot discover the schema for the view. Access caches schema
information for linked tables locally, so it can create the
parameterized prepared statement you are seeing in the first query.
Based on the Profiler output you posted, it *may* not do that for
views, so Access/Jet possibly has no idea of what the schema for the
view's underlying table is. Therefore it fetchs all of the data. You
might want to post in one of the msaccess forums in case the experts
there have a more definitive response.
--Mary
On Tue, 4 Oct 2005 05:35:02 -0700, "toni"
<toni@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> Hello Mary,
> I try to explain my problem, sorry for my english because i'm spain
> when i use directly a linked table with a local table, i have data
>immediatly, when I
> see the trace, Access send every value field (use in join) from data from
>the local table to the sqlserver.
>The profiler trace send :
>declare @.P1 int
>set @.P1=2
>exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT "field1" FROM
>"dbo"."table1" WHERE ("field1" = @.P1)', N'925000029010202145'
>select @.P1
>exec sp_execute 2, N'925000029010202146'
>exec sp_execute 2, N'925000029010202146'
>exec sp_execute 2, N'925000029010202146'
>Local table have 3 records
>when i do a simple view like this
>create view dbo.view as select field1 from dbo.table1
> when i use directly this linked view with a local table,
>the trace send :
>SQL:BatchCompletedSELECT field1 FROM "dbo"."view " Microsoft Access etc..
>and slqserver send millions records to access ...
>Why the linked view and the linked table not have the same reaction ?
>I can't do a view with more filter because every user have his owner local
>table.
>Thank you very much for your help.
>I can
>Rega1
>
>
>
>"Mary Chipman [MSFT]" wrote:
sql