Solved Access 2016 64 bit slow to open linked table Oracle 11 g 64 bit

[GForAccessForum]

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]

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]

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]

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:

 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.

 

[Minty]

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]

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]

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]

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]

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.

 

[Deleted member 147267]

cross posted https://www.accessforums.net/showthread.php?t=80209

GForAccessForum - please read https://www.excelguru.ca/content.php?184

 

[GForAccessForum]

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.

 

[Deleted member 147267]

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]

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]

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]

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.