[AccessD] Slo-o-o-w ODBC connection to Oracle

Also look at the load time of the combo box itself as this is "rolled in" to the load time of the 
main form.

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