MS Access 2010: Pass Through Query Slow to Return Single Value

First:  NEVER use hints.
Second: NEVER use hints.

>>The DBA claims that each invocation of my query takes about 0.1s

How may loops are you making?

Looks like you have more than enough debug statements.  Based on the d2 time, seems to be pulling the data back from the database to the client.

My VBA is rusty but why are you updating in a loop?
Why not do everything in a single transaction?

I just closed everything, logged out, and logged back in.  My application is now cranking through the queries.  Everything is taking <1s.

The reason I use hints:  We found out recently that our DBA's hadn't been running statistics.  We also have noticed that the same queries run fast or slow on a quasi-random basis.  I started using hints thinking that maybe Oracle was sometimes incorrectly guessing a bad query plan.

As far as loops go:  I'm not that interested in the overall time, just the time to run each Oracle query.  I am allowed to query the database, but not to write to it in any way.  My local table has 30,000+ rows so it would be difficult to pass that list of numbers via query to Oracle for processing.  I could do it in small batches, but I figured as long as each query doesn't take that long I can send them one-by-one.

So what I have learned is that somehow the connection from Oracle to MS Access gets gummed-up.  Has anyone seen this behavior before?  If so, is there a way to clear the blockage without having to kill everything and start it back up?

First, hints are there for a reason.  Don't use them unnecessarily and re-evaluate them often, especially with database upgrades.  Sorry, but I have seen way too many pieces of third party software that didn't allow you to do certain things in the database and the only way to get the correct query plan was to use a hint.  My favorite is a company whose performance tuning was done on a 6GB database and ours was over 150GB.  Quite a difference and their queries were not written correctly for a database the size their customers were actually using.

Fully custom databases with statistics that are up to date or very close shouldn't need hints (I still see exceptions to this).

As a general rule, I would recommend against hints.  I would not say never use them.

As far as something slowing down like you describe, sounds suspiciously like a memory leak.  You are getting to the point where you start paging and things start slowing down due to paging.

>>We found out recently that our DBA's hadn't been running statistics

Time to get a new DBA.  Seriously!!!

>>First, hints are there for a reason.  Don't use them unnecessarily and re-evaluate them often
>> shouldn't need hints (I still see exceptions to this).

Agreed.  They are there for testing.

>> I would not say never use them.

Never wasn't my words.  You can disagree with me but will you disagree with Tom Kyte way back from 2002?

1)When tuning an sql statement in plsql when should Hints be applied ?

and we said...
1) Never.  They are the path of last resort.

I agree that they have a very specific purpose and the CBO was buggy as H#LL several years ago.  Not so much these days.

Use hints to try out new things but I would never use them in production today.

>> sounds suspiciously like a memory leak.

Sort of agree.  

If the access database is open for a long time, are the connection objects and result sets being properly closed and disposed of when no longer needed?

Look at the memory usage on the machines over time.

I would definitely disagree with Tom Kyte.  Hints have a place.  They can be used in production code.  I have seen the CBO make the wrong choice many times.  Still does to this day.  It isn't perfect and it never will be.

You can say to use stored outlines, but those are really just hints in a different form.

Is this production code, or just testing code?
DCount is evil.  All the domain functions are.  Don't use them in favor of a recordset.
nRec = DCount("STF_ID", "MyLocalTable")
and why would you do this when, a few lines later you have
 dbSQL = "select stf_id, stf_last_dt from MyLocalTable order by stf_id"
  Set rs = CurrentDb.OpenRecordset(dbSQL, dbOpenDynaset)
And at that point
nRec = rs.RecordCount
Which is much more efficient can be issued?

Here you have a compound checking line
If Not rs2.EOF And Not IsNull(rs2.Fields(0)) Then
and really, not a great one.  You don't use BOF or EOF to test for an empty recordset
That's what recordcount is for!

It should be a nested If with your present SQL
If rs2.RecordCount >0 then
    if IsNull(rs2.Fields(0)) = False Then
'... actions
    End If
End If

Or better yet, refine your SQL WHERE clause
qd.SQL = "SELECT /*+ INDEX(MyOracleTable MyOracleTable_PK) */ MAX(TEST_DT) FROM MySchema.MyOracleTable WHERE STF_ID = " & rs!STF_ID

so that it cannot return a null value, and then you don't have to code to handle the nulls

I'm not sure what this is trying to do
     rtn = SysCmd(acSysCmdSetStatus, "Rec#" & iRec & ", tQd=" & d1 & ", tOra=" & d2 & ", tUpd=" & d3)
      DoEvents
But DoEvents is something that should flag caution.
It pauses the execution of the VBA, which is always going to result in longer run times

Now, I'm not an Oracle guy -- I run SQL Server -- but MAX(TEST_DT) stands out as a flag!
Aggregate functions are evil.
Select Top 1 whatever from sometable where A=B order by Something Desc
is almost always a better way to a get Max value of what you want than an aggregate function, because the query optimizer can really get a hold of that, where a MAX function doesn't really optimize as well.

My local table has 30,000+ rows so it would be difficult to pass that list of numbers via query to Oracle for processing.  I could do it in small batches, but I figured as long as each query doesn't take that long I can send them one-by-one.

Myself, I'd pull everything in one go to a 'temp' local table in Access and process from there.
Likely to be a lot faster and make your DBA happier, too, as you're hitting the DB once, and not 30K times.  0.1 seconds X 30,000 is a big number.  Pulling it all at once is likely to be MUCH smaller.

That technique is discussed here.
https://www.experts-exchange.com/articles/6692/Overcoming-unbound-continuous-forms-limitations-by-staging-data.html

That's my 2 cents, anyway.

Nick67:  All of the code I wrote is test code.  I realize that most of the code is not the most efficient way to write it, but I'm really focusing on the time it takes to get the data.  I can't get all of the records from the table because that table has billions of rows. The hint I am using is basically telling the database to use the primary key which it should choose all of the time, but maybe doesn't on occasion because either the statistics weren't run or Oracle's cost based optimization is not as whiz bang as everyone seems to believe.

The SysCmd and DoEvents are to update the Access interface so I can see that the code is executing and records are being returned.  I seldom use Dcount().  I was just using it to see how many records would need to be processed so that I could show progress in the Access Status Bar.  (Working 1 of 30,000,  Working 2 of 30,000, etc.)

I'm nowhere closer to figuring out why the same exact code sometimes takes 4s to return a record and sometimes 0.1s or less.

I looked into ODBC tracing.  The standard MS tracing log does not include timestamps.  There is a product from Progress called DataDirect, but it would take too much effort to get my company to approve it and allow me to download it to my computer.  Also, the timestamps might not be that effective since tracing puts a real burden on ODBC such that the times are all lengthened.

ODBC tracing seems more helpful to people who are looking for errors, i.e. it tells them exactly when a failure occurs.  But my code is not failing, it is just taking an inordinate amount of time to run.

Has anyone else had luck with ODBC tracing?  Or any other method for getting timing information?

I'm nowhere closer to figuring out why the same exact code sometimes takes 4s to return a record and sometimes 0.1s or less.

If you are searching a billion row table, you are definitely going to run into the law of averages.  Sometimes the needle is one the edge of the haystack, sometimes it's hidden under the last straw.

For testing, if you would use say 1000 local records, record the time at the beginning, run the full loop and then record the time at the end, you could get an average time per transaction.

Run that simulation multiple times with the same 1000 records and you'll be able to see if there's any significant variation in processing times.  If not, well your average transaction time will be what it will be.

My SQL is using the primary key so it's not looking through billions of rows.  It's more like looking through 10's of binary tree nodes, if even that.

I can't use local records because the problem is not with my SQL statement.  The problem is that there is some problem with the interaction among MS Access, ODBC, our computer network, and Oracle.  I'm trying to identify where the problem lies and it is quite difficult.

I am not the DBA and our DBA's are understaffed and overworked so it is difficult to get time to play around with server-side settings to see if the problem is there.

Not to quibble, but even with an index, there's one entry per row.
Small data types, grouped and ordered, but still boatloads of them.
the problem is not with my SQL statement.

I don't know about that, either.
You are passing in the whole SQL statement to your passthrough
qd.SQL = "SELECT /*+ INDEX(MyOracleTable MyOracleTable_PK) */ MAX(TEST_DT) FROM MySchema.MyOracleTable WHERE STF_ID = " & rs!STF_ID

Never mind that MAX(TEST_DT) seems like a red flag, but your are passing raw SQL through.
That's not how it's done on SQL server if you want performance.
Instead you write a stored procedure that's going to take a parameter and then your querydef SQL is something like
"Exec mySPFindMaxBySTF_ID " & rs!STF_ID
The procedure to do the lifting is on the server
SELECT /*+ INDEX(MyOracleTable MyOracleTable_PK) */ MAX(TEST_DT) FROM MySchema.MyOracleTable WHERE STF_ID = @STF_ID
and it optimizes an execution plan.

When you are passing raw SQL like you are, how is the server supposed to know that it should optimize what, to it, looks like an ad-hoc query?  It probably doesn't.

I know your DBA is overworked, but the five minutes it would take for him to create a proper stored procedure are likely going to be far preferable to you hitting his server with 30K ad-hoc query statements.

For security reasons I am not allowed to create stored procedures on the server.  I agree that would be best, but the two problems with this are that I am not allowed and also that this particular application has dozens of query structures.  I might be able to get the DBA's to allow me to create one or two stored procedures, but not dozens.  I could try and fold everything into one humungous stored procedure with a CASE statement, but I don't know if I wouldn't lose some performance having it run through all the cases.

As far as passing raw SQL to Oracle:  this is the way MS Access works.  There are things called "Pass Through Queries".  They are the most efficient queries possible in MS Access because they completely bypass the Jet engine and don't need to be translated/checked by it.  They go straight to Oracle.  If I send a bunch of similar queries to Oracle then Oracle is supposed to be smart enough to see that they are similar and use previously stored plans.  At least that is what I've been led to believe.

And once again, if the queries were always slow then whether or not my query is efficient would be something to be concerned about.  Instead what is happening is that the same query is sometimes executing really fast (because it happens to be well-formed) and sometimes it is executing real slow for reasons I have yet to determine.

>>but I don't know if I wouldn't lose some performance having it run through all the cases.

One procedure might have ALL possibilities but may not be one huge SQL statement...

>> If I send a bunch of similar queries to Oracle then Oracle is supposed to be smart enough to see that they are similar and use previously stored plans.  At least that is what I've been led to believe.

I doubt hard-parsing is your main problem here but it might be.

A lot depends on the Oracle version and how the DBA has things set up.  You aren't using bind variables so it may or may not reparse the statement.  

A good read on the subject:
https://docs.oracle.com/database/121/TGSQL/tgsql_cursor.htm#TGSQL848

Look at the cursor_sharing examples:

Usually, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following statements do not resolve to the same SQL area:

SELECT count(1) FROM employees WHERE manager_id = 121;
SELECT count(1) FROM employees WHERE manager_id = 247;

The only exception to this rule is when the parameter CURSOR_SHARING has been set to FORCE, in which case similar statements can share SQL areas. The costs and benefits involved in using CURSOR_SHARING are explained in "When to Set CURSOR_SHARING to FORCE".

>>and sometimes it is executing real slow for reasons I have yet to determine.

Without end to end tracing, you really have no way to determine this.  It might be the network and have nothing to do with either database.

By "end to end", I mean setting up tracing at the database level, Orace client level, network and remove PC.  Only then can you figure out where the slowdown is.

I think what Nick was thinking is that the same query might not always be the same:
SELECT /*+ INDEX(MyOracleTable MyOracleTable_PK) */ MAX(TEST_DT) FROM MySchema.MyOracleTable WHERE STF_ID = 123;
and
SELECT /*+ INDEX(MyOracleTable MyOracleTable_PK) */ MAX(TEST_DT) FROM MySchema.MyOracleTable WHERE STF_ID = 321;

Might run completely different at the Oracle database and choose different access paths.

You are still making 30,000 round trips to Oracle instead of one that passes back ALL the results you need.

You might be able to get it down to a single query without a stored procedure.  I'm far from an Access Expert but if you can get your 30,000 local table rows converted into something like XML you might be able to create one Oracle query to process all of them.

Your limitation might be the 64K limit of a pass-through query that I Googled and probably a 4,000 character limit for a string literal in Oracle.  Then you have ODBC limits when dealing with large strings...

Here is a very quick setup for what I mentioned above.

This is an Oracle setup.  Hopefully you can get the idea.  I made test_dt a number to make things easy.  Number or date shouldn't matter.

The setup and select:

drop table MyOracleTable purge;
create table MyOracleTable(stf_id number, test_dt number);
insert into MyOracleTable values(1,1);
insert into MyOracleTable values(1,2);
insert into MyOracleTable values(1,3);
insert into MyOracleTable values(2,2);
insert into MyOracleTable values(3,3);
insert into MyOracleTable values(3,4);
commit;


SELECT STF_ID, MAX(TEST_DT)
FROM MyOracleTable
WHERE STF_ID in 
	(
		select stf_id from xmltable(
			'/IDList/ID'
			passing xmltype('<IDList><ID>1</ID><ID>3</ID></IDList>')
			columns
				stf_id	number	path '.'
			)
	)
group by stf_id;

Select allOpen in new window

The results:

    STF_ID MAX(TEST_DT)
---------- ------------
         1            3
         3            4

Select allOpen in new window

With the above string constraints still in play, you might only have to dynamically change this string:
<IDList><ID>1</ID><ID>3</ID></IDList>

Want only 2?
<IDList><ID>2</ID></IDList>

etc...

One trip to the Oracle database and one result set passed back.

He's not allowed to write to the server.
I'm not an Oracle guy.
Are those like table variables in SQL Server?

Want to shave characters, you can reduce the XML size to single characters and attributes:

SELECT STF_ID, MAX(TEST_DT)
FROM MyOracleTable
WHERE STF_ID in 
	(
		select stf_id from xmltable(
			'/q/w'
			passing xmltype('<q><w e="1"/><w e="3"/></q>')
			columns
				stf_id	number	path '@e'
			)
	)
group by stf_id;

Select allOpen in new window

>>He's not allowed to write to the server.

I understand that.  The drop and create table is just my test case.

The SELECT is the only thing he'll need to pass to Oracle.  Assuming the local lookup can be converted to XML.

Good enough.
Thought I'd ask since I was going to suggest that he get a table definition setup to push his data to the server -- until I looked and remember that he can't write a damn thing to the db :)

@slightwv
How would you write a query to return a single value from a billion row table with a single constraint?
MAX() or with an Order by DESC and ROWNUM =1?

>>They get away from using MAX() by using an ORDER BY and ROWNUM and double the efficiency of the query.

Sorry bro but not the same...

First, that is from 2003.
Second:  If has a sub-query that looks for a column equal to the MAX.

Not the same as what is here.

I'll bet beer that MAX is faster than the ROWNUM approach in this instance!

Since that was 2003:
If ROWNUM works, then I'll wager ROW_NUMBER will out-perform it.  Same concept, different syntax.  Still bet it won't out-perform MAX.


Humorous but semi-relevant.  Not really the same thing but relates to something stated above.  If you read farther down in the link you posted you'll see:

hat is a terrible approach.

don't do that anyone!

if you have that in your code, get rid of that developer....


hints are hints, they are not directives, not commands.

>>How would you write a query to return a single value from a billion row table with a single constraint? MAX() or with an Order by DESC and ROWNUM =1?

Cross posted...  beer bet says MAX with no hints.

>>I was going to suggest that he get a table definition setup to push his data to the server

That might be brilliant!!!  That brings up an interesting idea:
Global Temporary Tables (GTT).

These are database objects that are created once and reused by ALL.

Only the session that inserts data into them can see the data.  They are automatically cleaned up when the session ends.

This might beat the H#!! out of my XML approach.

They could insert into the GTT from Access then issues a pass through that joins against it.

The DBA only needs to create a single object:

create global temporary table myLookupIDs(stf_id number) on commit preserve rows;

Select allOpen in new window

No PL/SQL code, no nothing.  Issue one single command and they are done forever...

Then any connection can insert the local ids into it, issue a master select and return the result set.

You just need to make sure the session ends and reconnects before loading up myLookupIDs or at least delete from it...

New setup and GTT example:

--DBA runs this only once
drop  table myLookupIDs purge;
create global temporary table myLookupIDs(stf_id number) on commit preserve rows;

--My test setup for Oracle table that mirrors their production
drop table MyOracleTable purge;
create table MyOracleTable(stf_id number, test_dt number);
insert into MyOracleTable values(1,1);
insert into MyOracleTable values(1,2);
insert into MyOracleTable values(1,3);
insert into MyOracleTable values(2,2);
insert into MyOracleTable values(3,3);
insert into MyOracleTable values(3,4);
commit;


--issued from Access:
insert into myLookupIDs values(1);
insert into myLookupIDs values(3);


--in the same session, this is the passthrough query
SELECT local.STF_ID, MAX(TEST_DT)
FROM MyOracleTable local join myLookupIds remote on local.stf_id=remote.stf_id
group by local.stf_id;

Select allOpen in new window

I am thinking about PARTITION.
Isn't that what he's really doing?

SELECT STF_ID, MAX(TEST_DT) OVER (PARTITION BY STF_ID) AS MAX_DT
FROM MySchema.MyOracleTable;

That will return them all.
Stuff them into a 'temporary table' in Access and then pull out the 30K that match the local table in one query.
Or just leave them in the recordset, and process it via VBA

slightwv and Nick67:

Thanks very much for all of your suggestions.  The discussion regarding MAX() is interesting.  I've seen articles where simple assumptions are often incorrect.  For example, for small lookup tables it is often quicker to do a full table scan rather than an indexed scan because the entire table is usually brought into memory, and for a full scan only the table has to be brought into memory not the index.  So a full table scan of a small table can often be twice as quick as an indexed scan.  At least that was true as of a few years ago.

I had thought that having very similar strings would be enough, but evidently it is not.  I wish MS Access had bind variables I used to use them in C programs when invoking the Oracle SQL API.  

I will look into seeing if I can get (or maybe already have) the privilege to create temporary tables.  This shouldn't be a big deal to the DBA's as far as security is concerned, but they may be concerned about performance.

I will also look into the table of numbers data type and see if I can get anywhere with that.  One application I wrote just creates a very long "where STF_ID in (101, 203, 402, ...)" clause.  The table of numbers data type looks like a better solution.

Your suggestions will certainly make this particular task run quicker.  However, it still doesn't answer the basic question of where the time lag occurs and why it is intermittent.  We are experiencing similar time lags with other applications that use MS Access front ends to access Oracle databases.  I was hoping that if I could solve the time lag issue for this application then I could apply that solution to all applications.

The prospect of having to trace everything from MS Access through ODBC through the network and through to the Oracle server, generating huge files, sifting through those files for useful data, and finally arriving at the AHA! moment is not one I am looking forward to.

>>just creates a very long "where STF_ID in (101, 203, 402, ...)" clause.  

IN lists are limited to 1,000 entries.  For your 30,000 you would have to dynamically generate 30 IN statements.

GTTs and PL/SQL collections are probably the way to go.

>> doesn't answer the basic question of where the time lag occurs and why it is intermittent

We cannot answer that.   Since it is intermittent, there is no "look here".  It could be a combination of all three pieces.  You have 30,000 individual transactions going back and forth as fast as they can.  I would probably look at networking or the local PC running Access for see what resource might be maxing out.

Do you have Virus scanning on that might be trying the scan the Access database while all this is going on?  Maybe it cannot keep up?

>>moment is not one I am looking forward to.

Sadly, a career in IT isn't always fun and games...

Thanks for all the useful tips.