[AccessD] Problem of a listbox's response on network... Part 1

Thank you Mark but your thanks are for Arthur. 

JIm

----- Original Message -----
From: "Mark Simms" <marksimms at verizon.net>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Monday, January 27, 2014 8:26:52 PM
Subject: Re: [AccessD] Problem of a listbox's response on network... Part 1

Jim - that is absolutely precious....and excellent.
That's what I was looking for....well done !
I am sure this technique is especially fast for updates and inserts....
which I found were dreadfully slow with non-passthru DAO queries.

> But if you use named queries, life gets a lot easier. Pass-Through Queries
> (PTQs)
> to the rescue! In essence, it boils down to this.
>
> a) Grab the syntax from your named query.
> b) Create a Stored Procedure or View on the back end using the same
> syntax.
> You might have to adjust it here and there to make it run. For example,
> you
> won't be able to use Access functions in the back end.
> c) Create a PTQ in the front end that points to the SP or View you just
> created
> d) In your Access code, use the syntas "CALL myPTQ."
>
> A few months back I worked on a project using an Access front end and a
> MySQL back end. The whole thing had been written to do all the queries
> in
> the front end. We identified a number of areas where a speed gain might
> be
> most profitable and began there, following the steps outlined above.
> The
> results were dramatic, to say the least -- sometimes a tenfold increase
> in
> performance.
>
> The main reason for this is that when querying a SQL back end from
> Access,
> the entire dataset is sent to the front end, which then applies the
> WHERE
> and ORDER BY predicates locally. When using a PTQ, Access gets out of
> the
> way and leaves the heavy lifting to the back end, which then sends only
> the
> final resultset to the front end.
>
> Arthur
>
>
> On Sun, Jan 26, 2014 at 1:11 PM, Mark Simms <marksimms at verizon.net>
> wrote:
>
> > Thanks Jim - and this implies anyone migrating from MDB to Oracle or
> SQL
> > Server has a complete rewrite on their hands as far as VBA-based data
> > retrieval, correct ?
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com