John W Colby
jwcolby at gmail.com
Tue Mar 12 14:13:02 CDT 2013
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> >> Website: http://www.databaseadvisors.**com<http://www.databaseadvisors.com> >>