Darryl Collins
darryl at whittleconsulting.com.au
Wed Mar 7 23:35:04 CST 2012
One way of doing the pre-processing without any coding etc is to use views in SQL server and refer to them rather than the underlying table. Say you have a very large dataset - use views to break it into logic segments that you commonly use (Say one set of views x Fin Year, Another set x State, or whatever). That way you can your database behave nice and fast for the user. For example if they are looking for all sales data in the date range of 1-March-2011 to 30-Jul-2011, you can then search only the view that has that range, rather than the whole darn table. You can even break it down further to suit your pre-defined queries. You can also get smart and use union to join up just the views you need if things wrap over. Using this approach can speed things up a lot as the volume of data to search thru is already limited. Cheers Darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Thursday, 8 March 2012 2:59 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Bind forms to ADO recordset John: Do not bind, especially that many records...set your SP to pull only the records needed or pull the records in groups by record number grouping...say record 1 to 1000, 1001 to 2000 and so on. Pulling 700,000 records is insanity...that is what MS SQL was created for. Pre-process first. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, March 07, 2012 6:51 PM To: Access Developers discussion and problem solving Subject: [AccessD] Bind forms to ADO recordset It is my understanding that binding a form to an ADO recordset allows a SQL statement to be "passed through" to SQL Server for very efficient processing, for example for sorting or selecting on the server. My question is, how do I prevent up front inefficiency in subforms. We know that Access uses Link Child / Master field properties to "narrow down" a set of records from the entire table to just matching records. What happens if I bind the subform to an ADO recordset? I can tell you from testing it that it is slower than sin! It does appear to pull the entire table into the sub form, and then perform the filtering. I was using a sql statement basically "SELECT * FROM TblXYZ" which is of course unfiltered in any manner. The specific table I am trying to pull data from has about 700K records. Yikes! Is anyone out there binding subforms to ADO recordsets and if so what is the trick? The only way I am making it work is to filter the subform using the PK of the main form in a dynamic sql statement, and essentially rebuilding the recordset in OnCurrent of the main form. Hardly pretty. -- John W. Colby Colby Consulting 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 Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com