John W Colby
jwcolby at gmail.com
Sat Mar 16 00:01:52 CDT 2013
I am completely rewriting an app originally done in Access. It was a combination of sophisticated and ... not. It already used a handful of pass through queries (about 6 or so) and yet made no effort to narrow down intermediate result sets using a starting data set. Let's just say that we know that a couple of hundred out of a hundred thousand loans are going to be processed. and yet over and over they pull all 100K records from 20 tables across the wire to filter it down later to 100 or so records. It was mind numbing slow. I started off just pulling the loan ID for the hundred, then joining that to other data sets. At least only the loan number field from the next table was pulled, and then the small data set after that. But eventually I went with a PTQ (pass through query) with CTEs (common table expressions). The starting loan numbers was the first CTE, then I joined that on the next table in a CTE to allow SQL Server to filter the next table down to the small number of records. Rinse and repeat over and over again. I end up with a couple of dozen CTEs all filtered down to the small number of records. At the very end I join all the CTEs to pull the required data. SQL Server is very efficient at this stuff it seems. Someone had designed a huge and quite "sophisticated" PTQ which even when executed out in SQL Server directly (in SSMS) took about 30 seconds to run. Simply by joining that query with the first small loan number set I dropped the time to a couple of seconds. Each of these previous PTQs pulled one small part of a mail merge data set. The problem was that it was pulling this stuff back into Access where they then joined these PTQs with about 25 other Access queries pulling data from ADO linked SQL Server tables (or views) layered up into a gigantic mess. I am reaching the end of completely rewriting the entire thing into one huge PTQ with (so far) about 20 CTEs right inside of this one PTQ. I am writing it in SSMS and just occasionally pulling it back into Access to make sure it still functions. So are the total time is perhaps 5 - 10 seconds to push it out of access to SQL Server, allow the server to parse this gigantic PTQ, execute the query, and pass the data back to Access. I had timed the pieces of the previous mess and was getting 2 minutes for one piece, three minutes for another and (untimed) but around 10 minutes for the final assembled piece. I was literally just going to the cafeteria while it ran. 10 seconds or so is quite acceptable to wait for the data from my PTQ IMHO. The icing on the cake is that as a single PTQ, the SQL statement can eventually be run from C# instead of Access. My new PTQ will be the business end and a little C# service can push the data into a CSV file for merging into the word doc. The old system had "Access functions and such embedded into the mess so that it would just never run on anything but Access. John W. Colby Reality is what refuses to go away when you do not believe in it On 3/14/2013 8:04 PM, Arthur Fuller wrote: > I'm a big fan of PTQs, too. In the absence of ADPs, which work only with MS > SQL, PTQs are a godsend > > > On Thu, Mar 14, 2013 at 5:36 PM, John W Colby <jwcolby at gmail.com> wrote: > >> I am going for the record here. I have a Pass Through Query which >> currently has 1006 lines of code including comments. I am commenting >> heavily as you might imagine. >> >> And no, I do not understand a great deal of it. Sigh. >> >> But at this point it is returning 544 records in just a few seconds. The >> previous query which this is meant to replace returned the same records in >> somewhere north of 10 minutes. How far north I will post when I get around >> to comparing my results to the old results. >> >> -- >> John W. Colby >> >> Reality is what refuses to go away >> when you do not believe in it >> >> -- >> 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> >> > >