tag:blogger.com,1999:blog-5710205.post459403424353998355..comments2024-02-08T11:00:53.069-06:00Comments on Gordon's Tech: Oracle ODBC access with 64 bit Windows 7JGFhttp://www.blogger.com/profile/14580785981874040314noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-5710205.post-59164403710993976552013-04-22T15:51:45.509-05:002013-04-22T15:51:45.509-05:00I just spent a week on this issue and I hope this ...I just spent a week on this issue and I hope this helps anyone still having the problem. If you have a slow connection with windows 7 and access, but you have a fast connection with windows XP and access, then this may help.<br /><br />It does not matter if you are using 32bit or 64 bit, the problem is with windows 7.<br /><br />If you are using windows 7 and access to connect to Oracle through an ODBC connection I might have an answer for you.<br /><br />Check the datatype of the primary key or index in the oracle table with slow access.<br /><br />If the column is of type NUMBER without anything else after it, this could be your issue.<br /><br />The solution is to change the datatype of the primary key or index columns to NUMBER(15,0) This is assuming your primary key does not need decimal points.<br /><br />Below will be what I believe the problem to be and how this fixes the problem for windows7.<br /><br />It seems to be that windows XP and access send a request to the oracle database treating the the index as SQL_DOUBLE, which access can handle just fine.<br /><br />Windows 7 and access send a request to the oracle database treating the index as SQL_FLOAT.<br /><br /><br />This is where I believe the problem happens.<br /><br />Oracle has to cast the index from a double(I know oracle doesn't have double for a data type, but NUMBER defaults to NUMBER(9,2) and stores it like a double), which is a double precision 64-bit floating point data type into a float, which is a single precision 32-bit floating point number, and when access gets the data from the ODBC, it converts it back to a double.<br />It is very CPU intensive for Oracle to convert 64-bit values into 32-bit values before replying to a query.<br /><br /><br />When oracle has the primary key stored as a data type number(15,0), windows 7 and access send a request to the oracle database treating the primary key as a decimal with 15 digits and 0 decimals. The data type decimal is a 128-bit floating point data type.<br /><br />Basically this means that oracle does not have to cram a 64 bit value into a 32bit value before sending it back to who ever requested the data as a float.<br /><br />I have ODBC trace files that indicate these results.Anonymoushttps://www.blogger.com/profile/18002753747013788040noreply@blogger.com