Strange Access Query Behavior with SQL Server
am 04.04.2008 22:36:13 von ysageev
Greetings,
I've been trying to optimize the performance of the queries in an
Access 2007 front end that utilizes SQL server for all tables
(linked). The application runs fine without error. However, when I
run SQL Server profiler it clearly shows that every entry in a
continuous subform results in an explicit, individual query to the
database.
I.e., instead getting a result set in one shot and populating each
entry in the continuous subform with a row from the returned result
set, which would be much faster, it queries across the network for
each entry. Is there a way to correct this? For example, Access runs
the following query multiple times for each form, or once for each
item.
(query has been shortened for readability, copied from profiler)
SELECT "DescriptionId","ProjectTitle","ItemNumber","Description" FROM
"dbo"."SomeTable" WHERE "DescriptionId" = @P1
Thanks!
Re: Strange Access Query Behavior with SQL Server
am 05.04.2008 07:31:35 von Tom van Stiphout
On Fri, 4 Apr 2008 13:36:13 -0700 (PDT), ysageev@gmail.com wrote:
How are you linking? Perhaps you could try different middleware?
Access ADP does some pretty funky querying as well, just in case you
thought that would be the panacea.
If you really think this is a problem, you may need a programming
environment where you have more control, such as VS.NET.
-Tom.
>Greetings,
>
>I've been trying to optimize the performance of the queries in an
>Access 2007 front end that utilizes SQL server for all tables
>(linked). The application runs fine without error. However, when I
>run SQL Server profiler it clearly shows that every entry in a
>continuous subform results in an explicit, individual query to the
>database.
>
>I.e., instead getting a result set in one shot and populating each
>entry in the continuous subform with a row from the returned result
>set, which would be much faster, it queries across the network for
>each entry. Is there a way to correct this? For example, Access runs
>the following query multiple times for each form, or once for each
>item.
>
>(query has been shortened for readability, copied from profiler)
>
>SELECT "DescriptionId","ProjectTitle","ItemNumber","Description" FROM
>"dbo"."SomeTable" WHERE "DescriptionId" = @P1
>
>Thanks!