Brad Marks
BradM at blackforestltd.com
Wed Mar 13 07:41:19 CDT 2013
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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.