Link to home
Start Free TrialLog in
Avatar of Massimo Brillante
Massimo Brillante

asked on

Querying Oracle from VBA slow only the first time

I am querying an Oracle database remotely from a VBA project; the query is below. The first time that I run this query is quite slow; however if I run it after, no matter the interval of elapsed time the query is quite fast. Is this to do with  the way that ADODB works?

Connection string:
Provider=msdaora;Data Source=HALOPRD; User Id=xxxxxx;Password=xxxxxxxxx

Open in new window


query

SELECT 
v.VALUE_DTIME, 
MAX( CASE WHEN v.MEAS_ASS_ID = 219273  THEN v.NUMERIC_VALUE END ) as COl23,
MAX( CASE WHEN v.MEAS_ASS_ID = 219270  THEN v.NUMERIC_VALUE END ) as Col24,
MAX( CASE WHEN v.MEAS_ASS_ID = 219271  THEN v.NUMERIC_VALUE END ) as Col25,
MAX( CASE WHEN v.MEAS_ASS_ID = 108351  THEN v.NUMERIC_VALUE END ) as Col26,
MAX( CASE WHEN v.MEAS_ASS_ID = 108384  THEN v.NUMERIC_VALUE END ) as Col27,
MAX( CASE WHEN v.MEAS_ASS_ID = 108383  THEN v.NUMERIC_VALUE END ) as Col28,
MAX( CASE WHEN v.MEAS_ASS_ID = 219929  THEN v.NUMERIC_VALUE END ) as Col29,
MAX( CASE WHEN v.MEAS_ASS_ID = 219926  THEN v.NUMERIC_VALUE END ) as Col30,
MAX( CASE WHEN v.MEAS_ASS_ID = 219927  THEN v.NUMERIC_VALUE END ) as Col31,
MAX( CASE WHEN v.MEAS_ASS_ID = 219507  THEN v.NUMERIC_VALUE END ) as Col32,
MAX( CASE WHEN v.MEAS_ASS_ID = 219504  THEN v.NUMERIC_VALUE END ) as Col33,
MAX( CASE WHEN v.MEAS_ASS_ID = 219505  THEN v.NUMERIC_VALUE END ) as Col34,
MAX( CASE WHEN v.MEAS_ASS_ID = 109004  THEN v.NUMERIC_VALUE END ) as Col35,
MAX( CASE WHEN v.MEAS_ASS_ID = 109037  THEN v.NUMERIC_VALUE END ) as Col36,
MAX( CASE WHEN v.MEAS_ASS_ID = 109036  THEN v.NUMERIC_VALUE END ) as Col37,
MAX( CASE WHEN v.MEAS_ASS_ID = 220805  THEN v.NUMERIC_VALUE END ) as Col38,
MAX( CASE WHEN v.MEAS_ASS_ID = 220802  THEN v.NUMERIC_VALUE END ) as Col39,
MAX( CASE WHEN v.MEAS_ASS_ID = 220803  THEN v.NUMERIC_VALUE END ) as Col40,
MAX( CASE WHEN v.MEAS_ASS_ID = 221015  THEN v.NUMERIC_VALUE END ) as Col41,
MAX( CASE WHEN v.MEAS_ASS_ID = 221012  THEN v.NUMERIC_VALUE END ) as Col42,
MAX( CASE WHEN v.MEAS_ASS_ID = 221013  THEN v.NUMERIC_VALUE END ) as Col43,
MAX( CASE WHEN v.MEAS_ASS_ID = 202026  THEN v.NUMERIC_VALUE END ) as Col44,
MAX( CASE WHEN v.MEAS_ASS_ID = 202059  THEN v.NUMERIC_VALUE END ) as Col45,
MAX( CASE WHEN v.MEAS_ASS_ID = 202058  THEN v.NUMERIC_VALUE END ) as Col46,
MAX( CASE WHEN v.MEAS_ASS_ID = 108536  THEN v.NUMERIC_VALUE END ) as Col47,
MAX( CASE WHEN v.MEAS_ASS_ID = 108541  THEN v.NUMERIC_VALUE END ) as Col48,
MAX( CASE WHEN v.MEAS_ASS_ID = 100061  THEN v.NUMERIC_VALUE END ) as Col49,
MAX( CASE WHEN v.MEAS_ASS_ID = 100066  THEN v.NUMERIC_VALUE END ) as Col50,
MAX( CASE WHEN v.MEAS_ASS_ID = 100079  THEN v.NUMERIC_VALUE END ) as Col51,
MAX( CASE WHEN v.MEAS_ASS_ID = 100092  THEN v.NUMERIC_VALUE END ) as Col52,
MAX( CASE WHEN v.MEAS_ASS_ID = 100069  THEN v.NUMERIC_VALUE END ) as Col53,
MAX( CASE WHEN v.MEAS_ASS_ID = 100068  THEN v.NUMERIC_VALUE END ) as Col54,
MAX( CASE WHEN v.MEAS_ASS_ID = 100143  THEN v.NUMERIC_VALUE END ) as Col55,
MAX( CASE WHEN v.MEAS_ASS_ID = 100142  THEN v.NUMERIC_VALUE END ) as Col56,
MAX( CASE WHEN v.MEAS_ASS_ID = 100002  THEN v.NUMERIC_VALUE END ) as Col57,
MAX( CASE WHEN v.MEAS_ASS_ID = 100003  THEN v.NUMERIC_VALUE END ) as Col58,
MAX( CASE WHEN v.MEAS_ASS_ID = 100004  THEN v.NUMERIC_VALUE END ) as Col59,
MAX( CASE WHEN v.MEAS_ASS_ID = 100005  THEN v.NUMERIC_VALUE END ) as Col60,
MAX( CASE WHEN v.MEAS_ASS_ID = 108592  THEN v.NUMERIC_VALUE END ) as Col61,
MAX( CASE WHEN v.MEAS_ASS_ID = 108591  THEN v.NUMERIC_VALUE END ) as Col62,
MAX( CASE WHEN v.MEAS_ASS_ID = 100117  THEN v.NUMERIC_VALUE END ) as Col63,
MAX( CASE WHEN v.MEAS_ASS_ID = 100116  THEN v.NUMERIC_VALUE END ) as Col64  
FROM HALO.T_MEAS_VALUE v  
WHERE v.MEAS_ASS_ID IN (219273, 219270, 219271, 108351, 108384, 108383, 219929, 219926, 219927, 219507, 219504, 219505, 109004, 109037, 109036, 220805, 220802, 220803, 221015, 221012, 221013, 202026, 202059, 202058, 108536, 108541, 100061, 100066, 100079, 100092, 100069, 100068, 100143, 100142, 100002, 100003, 100004, 100005, 108592, 108591, 100117, 100116) 
AND v.VALUE_DTIME BETWEEN TIMESTAMP '2020-06-23 04:00:00' 
AND TIMESTAMP '2020-06-23 04:00:00' 
GROUP BY v.VALUE_DTIME  
ORDER BY v.VALUE_DTIME

Open in new window

Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

It's rather the database, which has to parse the SQL statement at least 1 time. If this is executed multiple times afterwards without any changes, Oracle does not have to parse it again.
In addition, the results will be cached in one or another way... Surely, caching will be even more powerful if using EE features like result cache or such, but in general, even a XE edition will perform caching ;-)
Avatar of Massimo Brillante
Massimo Brillante

ASKER

>>If this is executed multiple times afterwards without any changes

even if the query runs first time slow then vba project is closed, opened up again in teh afternoon and it is fast?
How many rows are involved? Cause I would not expect that much from your predicate

AND v.VALUE_DTIME BETWEEN TIMESTAMP '2020-06-23 04:00:00' AND TIMESTAMP '2020-06-23 04:00:00' 

Open in new window

In this case you need to look into the existing indices. Your table should have one (VALUE_DTIME, MEAS_ASS_ID, NUMERIC_VALUE) or (MEAS_ASS_ID, VALUE_DTIME, NUMERIC_VALUE) depending on the selectivity.
only once at year 267 rows and then on a daily basis no more than 2 or 3 rows every time the query run
I attached a screenshot of the table; please note that I have only read permission on this database; I have not designed or implemented, I can only query it

User generated image

But as I said, the query is slow only the first time when it run in the morning; seems like the oracle server takes time to wake up :-)
for example now the code below that run the query I wrote in my question use this date range and it run in less than 2 seconds

GetHaloDataset("2020-03-31 04:00:00", "2021-04-01 04:00:00")

Open in new window

https://stackoverflow.com/questions/8111315/oracle-low-speed-in-first-execute#:~:text=2%20Answers&text=The%20reason%20why%20it%20takes,but%20executes%20the%20new%20query. 

It's all about caching in this case ;-)

If it would be a matter of missing or bad indexes and/or missing or bad statistics, the performance would always be as bad as the first exec....
only once at year 267 rows and then on a daily basis no more than 2 or 3 rows every time the query run
This sounds like primary reason uncached data, cause it is not really used. After running the query once a day, the data is in the buffer pool. So subsequent queries are faster. This does also hit query compilation. So the first time the query is compiled, it takes longer to read the necessary statistics.

What does the Index register show?

I attached a screenshot of the table; please note that I have only read permission on this database; I have not designed or implemented, I can only query it
Talk with the DBA, you probably get an index created.
The index show this

User generated image
Oracle may take MV_PK for exec plan... Or the CBO says "no", I'll take another route ;-)
I repeat: it's the cache!
Thanks Alex,

as the DBA is a third party COmpany and we have to "pay" them even to talk :-) I'll leave it for now as I am the only one complaining at the moment. If I do get any complain for the code to be slow then I will talk to the DBA and raise your suggestion(s)


ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Tomas,

is this how Oracle works? I have until now worked with SQL server databases and I have not had this problem ever.  This is my first experience with Oracle
Hi,

This is how every DBMS works.
If you have slow queries you need to investigate why by running EXPLAIN to see how the database is reading and fetching the data. If the data is fetched by tablescan or an index is read poorly (too much rows scanned) then there is a need for an index that suits that particular query better.
And every DBMS needs to be tuned according to the workload they get and process.

Regards,
    Tomas Helgi
Thanks Tomas,

I will investigate EXPLAIN and try to run it (hopefully I will be bale to with my permission) on this oracle database

If the data is fetched by tablescan then there is a need for an index that suits that particular query better. 
Not necessarily ;-)
Supossing, the stats are accurate and up-to-date, even in this case the CBO might chose another approach to fetch the data. Indexes are important, no question, but for the CBO, they're "just" helpful stuff to get his paths right...
Hi

For a query to utilize a DBMS resources as optimal as possible with minimal impact on other workload you want to avoid TABLESCANS as much as possible.
A TABLESCAN means that the entire table is read into memory and if your DBMS has not enough RAM to spare for the table then you slow everything down.

Regards,
   Tomas Helgi
Actually, you mean "Full Table Scans", BUT I disagree!! I have seen quite complex queries with many tables involved, where the CBO chose 1 or more FTS for the smaller ones! In the end, you have to/should trust the CBO ;-)

http://www.dba-oracle.com/oracle10g_tuning/t_tracking_full_tables_scans.htm 

Seeing a FTS in an exec plan doesn't mean it's bad in general!
Hi Alex,
In the link you provided it says
While we see that full-table scans are not evil, they do indeed         signal a “possible” tuning opportunity.  Unnecessary large table         full-table scans can cause a huge amount of unnecessary I/O, placing a         processing burden on the entire database.

This is why TABLESCANS should be avoided in general as I said before.

Regards,
    Tomas Helgi


This is why TABLESCANS should be avoided in general as I said before.
Wrong! FTS regarding large tables -> yes/ok, but NOT in general, this kind of generalization can be quite misleading and confusing, especially for beginners!
All colleagues are correct about the hard parsing you experience at the first run.
I see that your query is used many hard coded values like:

MAX( CASE WHEN v.MEAS_ASS_ID = 100061

and especially this:

AND v.VALUE_DTIME BETWEEN TIMESTAMP '2020-06-23 04:00:00'  AND TIMESTAMP '2020-06-23 04:00:00'

In order to eliminate further hard parsing try to use bind variables everywhere it is possible.

AND v.VALUE_DTIME BETWEEN TIMESTAMP :b_stamp  AND TIMESTAMP :e_stamp
So instead hard parsing oracle will use only soft parsing which is more fast.