On Sun, 15 Feb 2004 21:30:11 +0100, "Jegger" <dz****@net4u.hr> wrote:
Hello!
We have following situation;
network with 100 users, aplication developed in Access, user DB
deployed on SQL Server.
Is it better to create query inside aplication (with code) and then pass
it to SQL Server for execution or is it better to have all these queries
saved like stored procedures and then called from aplication?
Witch queries run faster?
Thank's!
P.S. I know that my english isn't perfect:(!!!!!
Since you say this is an application developed in Access, and there are,
apparently, no other applications sharing the back-end, my recommendation is
to implement all the queries in Access except those that you can't make run
optimally that way by simply modifying the query's approach (chaning
subqueries to outer joins, etc.). Most queries of linked tables on SQL Server
will run well as Access queries.
The reason for keeping the queries in Access as much as possible is that it
makes the particular choice of server easier to change. It would be
relatively (though not totally) trivial, for instance, to switch to a
PostgreSQL or Oracle back-end.
There is actually very little speed penalty, in most cases, for executing
Access queries against linked, ODBC tables because Access can generally
convert these into prepared statements that actually act quite a bit like
stored procedures in that they are compiled once by the server when they are
first used, then reused when the same query is executed again. SQL Server is
also clever enough to reuse the same query plan when additional statements are
prepared with identical definitions.
