John W Colby
jwcolby at gmail.com
Wed Oct 30 12:02:47 CDT 2013
Are you using a subform that is loaded dynamically? Access tries to load the subform data before the mainform data. If you leave the subform control unpopulated, load the main form, then insert / load the subform that MAY help. John W. Colby Reality is what refuses to go away when you do not believe in it On 10/30/2013 12:55 PM, Heenan, Lambert wrote: > x-posted to Access-L and Access-D > > I'm hoping someone out there has some experience of this. This is my first time working a non-Access back end. > > Using Access 2010 connected to an Oracle 10g back end and using a System DSN. > > It is HORRIBLY slow!! Here's the scenario. > > I have a table of people (~20,000 rows) and a table or related assets (~24,000 rows), and I'm displaying the data in a form/continuous sub form setup. The Main form and Sub form a linked via the Primary key of the people table and the corresponding foreign key in the assets table. > > The main form is bound to the people table via a simple select query. The record source of the form is set when a person is selected from an unbound combobox... > Me.RecordSource = "select * from MyQuery Where ID=" & ComboBox. > This same combobox selection also sets the record source for the continuous sub form > Me.View_Data_sub_frm.Form.RecordSource = "Select * from AnotherQuery Where FKID=" & ComboBox > > When the front end is hooked up to an Access back-end (and MDB file) it takes about 0.1 seconds to set the form record source, and another 5.5 seconds to set the sub-form record source and display the data. > > When connected to the Oracle back end the record source of the main form is a Pass Though query as it is calling a decrypt function in the Oracle back end, the record source of the sub-form is the same query as used with the Access back end. Setting the record source for the main form now takes about 70 seconds and it takes a further 40 seconds to set the sub form record source. In the first step - setting the main form record source, if I just use the original non-pass through query it takes about 40 seconds to select the data, and another 40 to get the related data. > > Any thoughts on improving these awful response times? > > Lambert > >