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> >