Symptoms
Hello, our group has a Service Instance that connects to a particular Oracle instance. We have a Smart Object created using this Service Instance to connect to a particular table. The table has about 300,000 records, and we're able to return the whole data set in about 2 seconds when using Oracle SQL Developer. However, when we try to execute List from the Smart Object Service Tester, or use the Smart Object in a form, the List execution takes about 140 seconds. Is there anything we can do to get the Smart Object to return this data set more quickly?
Diagnoses
After running a profiler trace, we determined that the query from the smartobject was causing the slowness in response.
The queries run from the various service brokers need to work well in a variety of situations, however, in some cases, these queries may not be the most optimal option. Unfortunately, there isn't a way at this time to directly edit the SQL query the service broker is running against the DB
Resolution
As a workaround, a stored procedure will be created to surface the same data with a more optimized query.