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

Doug Steele dbdoug at gmail.com
Tue Mar 12 14:57:13 CDT 2013


Thanks for the comments, John.  I have the luxury of being dbowner of the
Access databases that I'm converting to SQL back ends, so once I've
converted a big query to SQL I just run it as a stored proc.

Doug


On Tue, Mar 12, 2013 at 12:13 PM, John W Colby <jwcolby at gmail.com> wrote:

> 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
>
> Reality is what refuses to go away
> when you do not believe in it
>
> On 3/12/2013 1:25 PM, Doug Steele wrote:
>
>> Hi John:
>>
>> I'm interested that you aren't using stored procedures to run the queries
>> on the SQL server back end.  Do you have any comments?
>>
>> Thanks,
>> Doug
>>
>>
>> On Tue, Mar 12, 2013 at 10:11 AM, John W Colby <jwcolby at gmail.com> wrote:
>>
>>  I am in the process of converting local queries to pass through queries.
>>>   In order to do that I have to go construct the queries in SSMS, get it
>>> working, then pull the SQL back into Access, place it in the PTQ and save
>>> it.  Once I do that, the entire thing is run on SQL Server and only data
>>> returned.
>>>
>>> If the query is written in Access then "selection" data will be shipped
>>> just to decide what actual data needs to be pulled.  In general Access
>>> pulls both sides of joins and all fields in where clauses.  That is used
>>> to
>>> then ask for specific rows.
>>>
>>> John W. Colby
>>>
>>> Reality is what refuses to go away
>>> when you do not believe in it
>>>
>>>
>>> On 3/12/2013 12:12 PM, Brad Marks wrote:
>>>
>>>  All,
>>>>
>>>> This is just a curiosity question.
>>>>
>>>> Let's say that I have an Access application that obtains data from a SQL
>>>> Server database via ODBC.
>>>>
>>>> There are 1,000,000 rows in one of the SQL Server tables.
>>>>
>>>> There is a query in the Access application that returns 100 rows from
>>>> this table based on the "Where" condition in the query.
>>>>
>>>> I would guess that the "heavy lifting" is being done by the SQL Server
>>>> database box and only 100 rows are shipped back to the Access
>>>> application
>>>> on the PC.
>>>>
>>>> Is this correct?
>>>>
>>>> Is this always the case, or is it possible that all of the 1,000,000
>>>> rows
>>>> are sent back to the Access application depending on the complexity of
>>>> the
>>>> SQL?
>>>>
>>>> Again, these are just curiosity questions.
>>>>
>>>> Thanks,
>>>> Brad
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>  --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/****mailman/listinfo/accessd<http://databaseadvisors.com/**mailman/listinfo/accessd>
>>> <http:**//databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd>
>>> >
>>> Website: http://www.databaseadvisors.****com<http://www.**
>>> databaseadvisors.com <http://www.databaseadvisors.com>>
>>>
>>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd>
> Website: http://www.databaseadvisors.**com<http://www.databaseadvisors.com>
>


More information about the AccessD mailing list