Slow fetch with Oracle if dataset includes WideString and WideHMemo columns

Running some FireDAC performance tests and if I query a table and include the Memo column along with any of the WideString columns in the table then for some reason FireDAC fetches one row at a time where if I only query for the Memo column or the Memo column and other numeric columns it fetches 50 rows at a time.

Here is trace output from a query that selects EMP_SK and MEMO from the EMP table:

1090718206431 15:44:04.573          . OCIAttrGet [VType=ub4, AType=9, Result=0]
1090718206437 15:44:04.573          . OCIStmtFetch [ARows=50]
1090718208330 15:44:04.573          . OCIAttrGet [VType=ub4, AType=9, Result=50]
1090718208353 15:44:04.573         >> Fetched [Row=0]
1090718208397 15:44:04.573              . Column [N=1, Name=EMP_SK, Type=otNumber, Size=22, Data=-992147442998]
1090718208403 15:44:04.573              . OCILobLocatorIsInit
1090718208427 15:44:04.573              . Column [N=2, Name=MEMO, Type=otNCLOB, Size=4, Data=NULL]
1090718209056 15:44:04.574         << Fetched [Row=0]
1090718209065 15:44:04.574         >> Fetched [Row=1]
1090718209112 15:44:04.574              . Column [N=1, Name=EMP_SK, Type=otNumber, Size=22, Data=-992147442996]
1090718209117 15:44:04.574              . OCILobLocatorIsInit
1090718209220 15:44:04.574              . Column [N=2, Name=MEMO, Type=otNCLOB, Size=4, Data=NULL]
1090718209238 15:44:04.574         << Fetched [Row=1]

Just the MEMO column:

1086564979990 15:20:23.594          . OCIAttrGet [VType=ub4, AType=9, Result=0]
1086564980092 15:20:23.594          . OCIStmtFetch [ARows=50]
1086564981917 15:20:23.594          . OCIAttrGet [VType=ub4, AType=9, Result=50]
1086564981940 15:20:23.594         >> Fetched [Row=0]
1086564981977 15:20:23.594              . OCILobLocatorIsInit
1086564982006 15:20:23.594              . Column [N=1, Name=MEMO, Type=otNCLOB, Size=4, Data=NULL]
1086564982832 15:20:23.595         << Fetched [Row=0]
1086564982845 15:20:23.595         >> Fetched [Row=1]
1086564982853 15:20:23.595              . OCILobLocatorIsInit
1086564982871 15:20:23.595              . Column [N=1, Name=MEMO, Type=otNCLOB, Size=4, Data=NULL]
1086564982877 15:20:23.595         << Fetched [Row=1]

Then the incredibly slow fetch when I select ID and MEMO:

1086110768135 15:17:48.191          . OCIAttrGet [VType=ub4, AType=9, Result=0]
1086110768148 15:17:48.191          . OCIStmtFetch [ARows=1]
1086110769768 15:17:48.191          . OCIAttrGet [VType=ub4, AType=9, Result=1]
1086110769780 15:17:48.191         >> Fetched [Row=0]
1086110769795 15:17:48.191              . Column [N=1, Name=ID, Type=otNChar, Size=20, Data='     22011']
1086110769847 15:17:48.191              . OCILobLocatorIsInit
1086110769891 15:17:48.191              . Column [N=2, Name=MEMO, Type=otNCLOB, Size=4, Data=NULL]
1086110769903 15:17:48.191         << Fetched [Row=0]
1086110769937 15:17:48.191          . OCILobLocatorIsInit
1086110769974 15:17:48.191          . OCIDescriptorFree [HKind=OCI_DTYPE_LOB, HVal=$08DDEB18]
1086110769997 15:17:48.191          . OCIDescriptorAlloc [HKind=OCI_DTYPE_LOB]
1086110770046 15:17:48.192          . OCIAttrGet [VType=ub4, AType=9, Result=1]
1086110770063 15:17:48.192          . OCIStmtFetch [ARows=1]
1086110771491 15:17:48.192          . OCIAttrGet [VType=ub4, AType=9, Result=2]
1086110771503 15:17:48.192         >> Fetched [Row=0]
1086110771543 15:17:48.192              . Column [N=1, Name=ID, Type=otNChar, Size=20, Data='     22017']
1086110771560 15:17:48.192              . OCILobLocatorIsInit
1086110771597 15:17:48.192              . Column [N=2, Name=MEMO, Type=otNCLOB, Size=4, Data=NULL]
1086110771626 15:17:48.192         << Fetched [Row=0]

We don't see this slow performance problem with MSSQL.What causes FireDAC to behave the way it does when we query for columns that include both WideString and WideHMemo column types against an Oracle 12c ( database? This is using TFDQuery with Mode = fmAll and with Items = [fiBlobs, fiDetails, fiMeta]. RowsetSize is 50.

John Hansen
7/24/2015 11:01:55 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 16

A case was opened for this issue: 00434984

The current status is that the FireDac Oracle driver uses RowSetSize=1 when there are several NVARCHAR fields or one NVARCHAR field and at least one LONG or BLOB/CLOB fields. This due to a workaround for a bug in the Oracle Call Interface with OCI v9 several years ago. R&D will test against OCI v 12 and see if bug has been fixed and if the FireDac driver can be updated.

David Skelton
7/29/2015 3:34:55 PM

Similar Artilces:

FETCH Error : Mismatch between retrieve columns and fetch columns
Hi, everybody Procedure under gives a FETCH error. I try to get OUTPUT parameters not result set. Procedure are of Sybase ASE type and code are stored inside a transaction (This) What gives the error : Mismatch between retrieve columns and fetch columns ? How to get OUTPUT parameters from this procedure ? DECLARE Melding PROCEDURE FOR ssp_vi_msg_out @ip_session_id = :id_SessionID , //Innparameter @ip_nrdbmsg_id = :ad_MsgID , //Innparameter @ip_nrdbmsgtype_id = :ai_TypeID , //Innparameter, Resten... retur parametrene for en melding @op_msg_sender = :ls_...

column datatype in oracle and dataset table
 Hi all,I have an Oracle table with column of datatype NUMBER(10). When I add a tabledapter in dataset designer, it creates a table with a column of datatype: system.decimalI change it to system.int32, but when I use an insert query, the parameter is of decimal format. I changed the type in designer.vb (although it is not recommended) and it worked fine. but after time, it revoked back to decimal.Am I missing something, or it is just another oracle, .NET misunderstanding?  I would appreciate any feedback,Thanks,  I found the solution. When creating the Insert query, it ...

How to fetch the column from the Dataset using Querystring
Hi,       I have 'n 'columns in the dataset. Now i want to fetch the required column from the dataset and bind in the gridview. So how to fetch a required column using Query string tyep???? Thanks in Advance  No gain without pain...

Name: Tom Horstman Email: th577atyahoodotcom Product: Firefox Summary: SLOW SLOW SLOW Comments: I have been a user for 2-3 years. It seams that each "new" version gets slower and slower to load the pages. The latest is the slowest. Just some feed back to think about. MS Explorer is now much faster than Firefox. Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv: Gecko/20070309 Firefox/ ...

Name: Jack Inyart Email: jackatmacinyartdotcom Product: Firefox Summary: slow.....slow.....slow Comments: I recently downloaded and installed FireFox 3. I am so very disapppointed in the product, in contrast to my previous experiences with FF2. Firefox 3 is so slow to load any page that I am seriously considering returning to either Internet Explorer or, if possible, removing FF3 and returning to FF2 if that would be possible. I have been running comparisons on "time to load" between FF3 and IE 7 and the IE 7 loads pages in about 1/10 to 1/5 the time that ...

Slow, slow, slow
Name: Joe Goddard Email: sgoddar1atsandotrrdotcom Product: Gran Paradiso Summary: Slow, slow, slow Comments: I just installed Firefox 3 beta 1. The start up is still painfully slow compared to Safari. Is this software "bloat"? Browser Details: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O 10.4; en-US; rv:1.9b1) Gecko/2007110903 Firefox/3.0b1 ...

Slow slow very slow
Hi I have updated my opensuse 11.2 to 11.3. i use it on HP Laptop pavillion when the PC boot, i wait at least 5 min before to get kdm screen login i have look at the boot text console, and saw opensuse is block on line sm_notify " "idmapd" any idea ? i don't think is a nfs problem but ? regards -- Enthalpie ------------------------------------------------------------------------ What is the output of: Code: -------------------- rpm -qa | grep courier-imap -------------------- and Code: -------------------- ...

slow slow slow
Name: Alex Fitzsimmons Email: monkeyboyfactoryatnetscapedotnet Product: Firefox Summary: slow slow slow Comments: Since the last batch of updates were released (2007) all my Mozilla software has started to experience painful opening procedures. What did you guys do? These programs now take forever to open. Sometimes it is more effective to shut it down and use the 2nd attempt to open the software. Mozilla is the ONLY program that has developed this problem. I am disappointed because it has driven my girlfriend (A Mac using Video/Visual artist) to go back to using IE. Now tha...

Name: Product: Firefox Summary: TOO SLOW SLOW SLOW Comments: I'm sending this because i have always been a Firefox user but recently I have been disappointed in how slow or delayed it has become. at the present time I have switched to usimg Google Chrome because of its quick response time,however I often check back to see if this problem has resolved. As present I don't see any improvement but I hope this issue is addressed and resolved so I can return to Firefox. Thanks and Good Luck!! Browser Details: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv: G...

What could be the cause to slow down the Netware server. Any command type on the console it takes quite sometime to execute. Sometime take couple of minutes. I had unload all the services & modules. I had run the RAID check consistency & vrepair, but no help. The server is installed with NW5.1 SP4 & running BM3.5 sp3 but i had unload the BM modules. William C. wrote: > What could be the cause to slow down the Netware server. > Any command type on the console it takes quite sometime to execute. Sometime > take couple of minutes. I had unload all the ...

Slow slow slow
Name: Product: Firefox Release Candidate Summary: Slow slow slow Comments: Firefox has gotten so slow as of late it's incredible. I've had switch back over to safari for some tasks just so I can say... look something up on IMDb. I still love you firefox! Browser Details: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US; rv:1.9.2b5) Gecko/20091204 Firefox/3.6b5 From URL: Note to readers: Hendrix gives no expectation of a response to this feedback but if you wish to provide one you must BCC (not CC) the sender for them to see it...

slow, slow, slow!!!
Name: Phil Mishler Email: pmish20atgmaildotcom Product: Firefox Release Candidate Summary: slow, slow, slow!!! Comments: Just installed Firefox 3.6 beta 5. Three times I closed it out and reloaded. Each time it took 2 min. 15 sec. to load. Chrome takes 2 seconds. Guess which I will use. Browser Details: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.2b5) Gecko/20091204 Firefox/3.6b5 (.NET CLR 3.5.30729) From URL: Note to readers: Hendrix gives no expectation of a response to this feedback but if you wish to provide one you must BCC (...

Slow, Slow, Slow....
For some unknown reason, Thunderbird is running as slow as molasses=92...Is there anything that can be done to speed the emailer up?? Thank You.. On 05/07/2011 10:26 AM, Navy wrote: > For some unknown reason, Thunderbird is running as slow as > molasses�...Is there anything that can be done to speed the emailer > up?? > > Thank You.. > This could be related to your other problem so take it one thing at a time. Under Help menu, open troubleshooting information and paste it to another post here. Might show something (BTW, while there, Opening Containing Fo...

How to include a text file on a BLOB Oracle column?
Hi people! In my program, I construct a text file that, at end, I want to save it on a BLOB column in an Oracle table. Can could I do it? Thank you all in advance! Assuming you are writing a VBScript program to work in PowerDesigner. Then you can read and write to a database via ODBC. See the ODBCConnect.vbs sample script in the VB Scripts directory for an example of an ODBC connection and execution of a SQL command. HTH Jay Stevens [TeamSybase] On 15 Jul 2008 11:50:42 -0700, SEAD wrote: >Hi people! > >In my program, I construct a text file that, at end, I wan...

Web resources about - Slow fetch with Oracle if dataset includes WideString and WideHMemo columns - embarcadero.delphi.firedac

Mimas, A Moon of Saturn
Mimas is an inner moon of Saturn and looks somewhat like a bull's eye.

infrequent grumblings of a software engineer and then some... (also some Delphi programming)

AsyncCalls 2.99 – Asynchronous function calls
There will be no further development. With AsyncCalls you can execute multiple functions at the same time and synchronize them at every point ...

Remko Weijnen's Blog (Remko's Blog)
 Ever wondered how mstsc saves passwords? If you open an RDP file with a text editor like Notepad you can see the encrypted password. In this ...

Berkelium include/berkelium/WeakString.hpp Go to the documentation of this file. 00001 /* Berkelium - Embedded Chromium 00002 * SafeString.hpp ...

Reference for unit 'System': Types
[ Overview ][ Constants ][Types][ Classes ][ Procedures and functions ][ Variables ][ Index ] Reference for unit 'System' ( #rtl ) Reference ...

DIConverters [Delphi Inspiration]
... automatic character conversion for both reading and writing Unicode text. With DIUnicode , all text operations take place on a WideChar / WideString ...

Berkelium::Script Namespace Reference
Berkelium Berkelium :: Script Berkelium::Script Namespace Reference Classes class Variant Functions WideString BERKELIUM_EXPORT toJSON (const ...

Berkelium::Script::Variant Class Reference
Berkelium Berkelium :: Script :: Variant Berkelium::Script::Variant Class Reference #include List of all members. Public Types enum Type { JSSTRING ...

include/berkelium/WeakString.hpp File Reference
... Berkelium::WeakString Namespaces namespace Berkelium Typedefs typedef WeakString Berkelium::URLString typedef WeakString Berkelium::WideString ...

Resources last updated: 1/6/2016 12:28:58 PM