Solved Access 2016 64 bit slow to open linked table Oracle 11 g 64 bit (1 Viewer)

GForAccessForum

New member
Local time
Today, 13:20
Joined
Apr 14, 2020
Messages
7
Good evening, I have a problem with ACCESS 2016 64 bit and Oracle 11g 64 bit. When opening rather large linked oracle tables, the opening is very slow (some minute o open). If instead a pass-through query (like select * from <table>) is launched, the opening is immediate. The same tables opened with windows XP and Access XP with ODBC driver 9 are very fast. Can anyone help me please?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:20
Joined
Jul 9, 2003
Messages
16,244
I noticed your post has yet to receive a reply. I'm replying to give you a boost up the list so that the question gets another look by our members hopefully you will get an answer!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:20
Joined
Jul 9, 2003
Messages
16,244
When opening rather large linked oracle tables, the opening is very slow

I haven't got any particular experience with your problem, however as a general rule, you are much better off looking at a snapshot of Records instead of a whole table.

The lower the number of Records you are interrogating then obviously the quicker the operation is going to be. So is there any particular reason that you want to open a whole table?
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:20
Joined
Jan 23, 2006
Messages
15,361
With pass through query the processing is done on the server (Oracle). As Tony said when processing linked tables, it is advisable to put some constraint on the "data" to be returned. That is, try to use a query such as:
Code:
 Select * from YourLinkedTable
WHERE  OrderDate Between #2/13/20# AND #3/15/20#;
Without the constraint, the entire linked table will be passed back to Access.
 
Last edited:

Minty

AWF VIP
Local time
Today, 12:20
Joined
Jul 26, 2013
Messages
10,354
I agree with the above, in most cases you very rarely need the whole table.
On a form you are much better to load a bound form with something like
SELECT * FROM YourTable WHERE MainID=0
Then get the record(s) you want based on some user input.
 

GForAccessForum

New member
Local time
Today, 13:20
Joined
Apr 14, 2020
Messages
7
Thanks for the replies.
Opening the whole table is useful to immediately apply the filters and see the contents of the fields before setting up any queries. In addition, queries are very slow even using indexes. With pass-through query this does not occur. I thought of a slowdown due to the possibility of updating the records, as if Access 2016 or the Oracle 11 ODBC drivers were using a different and much slower transaction management system than the 32 bit with Oracle 9 where the opening operations they are very fast. I have the feeling that it is the setting of some parameters and it seems strange to me that this problem is not known to those who use Oracle interfaced with Access.
 

GForAccessForum

New member
Local time
Today, 13:20
Joined
Apr 14, 2020
Messages
7
I have found the problem but i don't know how can i solve. The problem is on the key table definition. If the key is set on varchar2 field the table opening operations is very fast, if the key is set on NUMBER field the table opening operations is very slow.
Anyone have any ideas on this?
 

Minty

AWF VIP
Local time
Today, 12:20
Joined
Jul 26, 2013
Messages
10,354
I'm not familiar with Oracle as such, but that sounds as if the Key (I'm guessing this translates as the primary key index in SQL), may not be unique for some reason.
Normally a indexed (Primary) key field is unique.

When the link to the table is set up did you identify the primary key?
 

GForAccessForum

New member
Local time
Today, 13:20
Joined
Apr 14, 2020
Messages
7
Yes, i identify the primary key. I create two tables (tot records 5.400.000); the tables are identical except for the primary key which in one case is VARCHAR2 and in the other is NUMBER. The one with the key VARCHAR2 is very fast, the other is very slow.
 

GForAccessForum

New member
Local time
Today, 13:20
Joined
Apr 14, 2020
Messages
7
I'm sorry, but on 04-07-2020 i post my messagge in Access Forums and I have not received any response. On 04-14-2020 (seven days later) i post my message here. I honestly didn't think of removing the post from the other forum. I thought it correct to let the other group know what the theme was developing and in fact I reported the partial solution also on the other. If it is a mistake please remove my post from here. Thanks and sorry again.
 

Micron

AWF VIP
Local time
Today, 08:20
Joined
Oct 20, 2018
Messages
3,476
As the article says, cross posting is OK as long as you declare it. Even nicer if you put link(s) in each forum.
Not my call to remove a post from anywhere but even if I could, here is where you're getting answers so that wouldn't be right. What you can do is post a solution or link to a solution from here in your other thread, assuming you get it resolved. Then you'd help someone else who comes across your other post.
 

GForAccessForum

New member
Local time
Today, 13:20
Joined
Apr 14, 2020
Messages
7
I would have been wrong to publish only the eventual final solution, as I did for the partial one. Thank you and excuse me.
 

GForAccessForum

New member
Local time
Today, 13:20
Joined
Apr 14, 2020
Messages
7
The problem can be solved in two ways:
1 - change the type of field on the oracle tables bringing it to NUMBER (10) or VARCHAR (2)
2 - on the configuration of the ODBC set "BIND NUMBER AS FLOAT" = true (BNF=T). In this case it is however necessary to create a new DB and recreate the linked tables.
Thank you all.
 

Isaac

Lifelong Learner
Local time
Today, 05:20
Joined
Mar 14, 2017
Messages
8,738
You got something you are satisfied with, but I just figured I'd throw in this idea - hope you don't mind. If I were in the same situation, faced with an option to link to an Oracle 'table' via ODBC which was slow to open but I wanted to open it sometimes just to view data before setting up queries, I might set up a passthrough query that only selects the top N (1000, maybe), or in Oracle, where rownum<1000. Then open that as your table for examination as needed. Just another idea.
 

Users who are viewing this thread

Top Bottom