[AccessD] Access and SQL Server - Where is the work done - How Many Rows are Shipped Across the Network

Hi John

Shorthand writing? SP, CTE, PTE, PTO, PTQ, IOW, SQL, SSMS ... but John W.
Colby in lieu of JC?

/gustav

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af John W Colby
Sendt: 12. marts 2013 20:13
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Access and SQL Server - Where is the work done - How
Many Rows are Shipped Across the Network

I may turn them into SPs, but probably not.  I inherited an Access database
with a set of about 32 queries, consisting of three "sets" each building a
temp table (in access), and at the very end the tables were joined to create
a result set which is exported to form the basis of a merge letter.

The total thing took about 10 minutes to run.  I am slowly working my way
through the queries, turning each into a CTE in a single query which
basically replaces the first temp table.  It is just painstaking work and to
this point I am always moving the built up data into a PTE back in Access
simply because it works and that PTQ can be joined to other queries.  The
PTO I just finished takes
2 seconds to run vs 120 seconds for the Access query mess it replaces.

The PTO is STILL a mess, but the mess is a bunch of common table expressions
in a single query, at the very end referencing each other in the same manner
that the query mess back in access does. IOW I have moved each subquery into
a CTE and then joined those CTEs in the PTQ.

It turns out that a pass through query whose SQL takes perhaps 2 seconds in
SQL Server directly, only takes perhaps 5 seconds as a PTQ back in Access.

Eventually I hope to have the entire thing be a single query out in SQL
Server but I have to constantly compare it back to the original to make sure
that it gets the same data.

The problem that I have with eventually moving it entirely into SQL Server
is that in my environment, I work in a copy of the real server.  The server
has perhaps a hundred databases, each database can have hundreds of tables
and views.  In order to create anything permanent out in SQL Server I have
to not only create it out there, but get permission to make the same changes
to the "real" production server (and a quality server and a user testing
server).  IOW I can create a PT query qhich has SQL which references
SomeDb.dbo.SomeTbl.SomeField and build up SQL that way.  Once it runs out in
SSMS I simply cut and copy that into Access and don't even save the original
out in SSMS.  Access acts as the repository of SQL which I can't actually
(or easily) store out in production land.

John W. Colby