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

SSMS - SQL Server Management Studio
SP - Stored procedure - a "procedure" stored out in SQL Server which can have parameters passed in 
and can return values or even tables.
PTQ - Pass Through Query - SQL statement designed in SSMS and only executable in SQL Server, but 
stored in Access in a special type of query.  It "passes through" the contained SQL code to SQL Server.
PTO - mis-spelled PTQ
CTE - common table expression.  A subquery inside of a larger query.  CTEs are placed at the top of 
a large query and gather data into recordsets which can then be referenced, joined to etc lby other 
CTEs or by the final SQL Statement.

I have a table of several million loan records, plus many MANY additional tables with other data 
about the loans.  However I have a couple of hundred loans which fall within a 15 day window out 
from today.  So I build a CTELimiting which pulls just the loan number for those few hundred.  Once 
I have that I can join to that CTE to limit other CTEs which are pulling other pieces of the various 
loan databases / table's records.  Instead of pulling a million records to get these fields from 
this table and a million records from that table and a million records from this other table, I join 
to the cte_limiting and only pull the few hundred records from each of those tables.

Because this cte_Limiting is the very first CTE loaded, SQL Server now uses that CTE just as it 
would a view or something, and can join on it etc.  SQL Server takes the "big picture query" and 
compiles it into an execution plan and goers to work processing much smaller chunks of all of these 
various tables.

In the end all of this huge query with several dozen CTEs and a final SELECT from these CTEs joined 
together selects a set of fields from a set of tables and hands back a small set of records with a 
bunch of fields from all over hell and back.  That huge SQL statement is made to work out in SSMS 
and then eventually cut and pasted into a pass through query back in Access.  The difference between 
pulling all of this data into Access and processing all the joins and wheres locally is a couple of 
orders or magnitude faster.

John W. Colby

Reality is what refuses to go away
when you do not believe in it

On 3/13/2013 8:41 AM, Brad Marks wrote:
> All,
>
> Thanks for all of the insights into this question.
>
> The answer is more complicated than I had thought.
>
> Brad
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com on behalf of Gustav Brock
> Sent: Wed 3/13/2013 4:27 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Access and SQL Server - Where is the work done - HowMany 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
>
>
>
>