I am tapping into an Oracle database that is hosted at an ASP. I connect via ODBC. Simply, I prompt the user for a project number. Then I query the database based on that project number to return a customer name for that project. Here is how I'm doing it:
------------
Dim rsCustomer As ADODB.Recordset
Set rsCustomer = New ADODB.Recordset
rsCustomer.Open "SELECT CustomerName FROM tblProjects WHERE ProjectNo = '" & txtProjectNo & "'", CurrentProject.Connection, adOpenStatic, adLockOptimistic txtCustomerName = rsCustomer.Fields("CustomerName")
rsCustomer.Close
------------
Here is my problem. It is WAY too slow. Either times out, or even when it completes successfully, I could never subject my users to dealing with the wait. I know that my bandwidth, and my ASP's bandwidth affect speed, as does the structure and maintenance of the Oracle DB. But I have no control over those.
What I'm wondering is if I'm using the most efficient code to query that database and bring the record back? I want to make sure that I'm doing everything on my end to keep the query clean and simple. If I could avoid having to do a nightly "dump down" to keep a local copy of the database, I'd like to stay live. Thanks for the help!
------------
Dim rsCustomer As ADODB.Recordset
Set rsCustomer = New ADODB.Recordset
rsCustomer.Open "SELECT CustomerName FROM tblProjects WHERE ProjectNo = '" & txtProjectNo & "'", CurrentProject.Connection, adOpenStatic, adLockOptimistic txtCustomerName = rsCustomer.Fields("CustomerName")
rsCustomer.Close
------------
Here is my problem. It is WAY too slow. Either times out, or even when it completes successfully, I could never subject my users to dealing with the wait. I know that my bandwidth, and my ASP's bandwidth affect speed, as does the structure and maintenance of the Oracle DB. But I have no control over those.
What I'm wondering is if I'm using the most efficient code to query that database and bring the record back? I want to make sure that I'm doing everything on my end to keep the query clean and simple. If I could avoid having to do a nightly "dump down" to keep a local copy of the database, I'd like to stay live. Thanks for the help!
