Page 1 of 1
Local sort on CLOB possible issue
Posted: Mon 19 Jul 2010 04:15
by MarkF
I'm attempting to perform a local sort on a CLOB column. It seems that if I sort a small number of records (say 100), it works fine. If the number is higher than about 200 records it seems to freeze up (or take a really long time.) I appeared to get a stack overflow error at one point, but that hasn't repeated. Should CLOBs be sortable? Is there anything to be aware of? The CLOBs in this particular table are not huge, they vary from 100 to 8000 characters. I've tried it with both deferred and non-deferred (but cached) options and it doesn't seem to matter. Latest ODAC under D2010, UnicodeOCI on. Thanks for any help.
-Mark
Posted: Wed 21 Jul 2010 14:53
by bork
Hello
I cannot reproduce your issue. Try to create a new application, add a new button and add the following code on the button click event:
Code: Select all
var
i, j: integer;
len: integer;
str: string;
MySession: TOraSession;
MyQuery: TOraQuery;
StartTime, FinishTime: TDateTime;
begin
Randomize;
MySession := TOraSession.Create(self);
MySession.Username := 'scott';
MySession.Password := 'tiger';
MySession.Server := 'ORA1110';
MySession.Options.UseUnicode := True;
MySession.Open;
try
OraSession1.ExecSQL('drop table test_table', []);
except
end;
OraSession1.ExecSQL('CREATE TABLE test_table ( ' + #13 +
'ID NUMBER, ' + #13 +
'NAME VARCHAR2(50), ' + #13 +
'MYCLOB CLOB, ' + #13 +
'CONSTRAINT pk_test_table PRIMARY KEY (ID) ' + #13 +
')', []);
MyQuery := TOraQuery.Create(self);
MyQuery.Session := MySession;
MyQuery.SQL.Text := 'select * from test_table';
MyQuery.Open;
for i := 1 to 200 do
begin
str := '';
for j := 0 to RandomRange(100, 8000) do
str := str + Char(RandomRange(byte('a'), byte('z') + 1));
MyQuery.Append;
MyQuery.FieldByName('ID').AsInteger := i;
MyQuery.FieldByName('Name').AsString := 'test ' + IntToStr(i);
MyQuery.FieldByName('myclob').AsString := str;
MyQuery.Post;
end;
ShowMessage('Start sorting');
StartTime := Now;
MyQuery.IndexFieldNames := 'myclob';
FinishTime := Now;
ShowMessage('Data is sorted: ' + FloatToStr((FinishTime - StartTime) * 24 * 60 * 60) + ' sec');
end;
On my computer this code was executed successfully and it takes 14 seconds. Please provide me the result of executing this code. If this code is working correctly then please modify this code to reproduce your issue.
Posted: Wed 21 Jul 2010 17:56
by MarkF
Thanks for looking into this. I also can't get your example to fail the way that mine does. Does it make sense that the sort takes 14 seconds (22 for me, I must have a slow computer!) to complete? If you modify your example to put the same data into the varchar field (shortening it to a max of 2000 chars) sorting the varchar field takes less than a second. Sorting the same data as a CLOB takes so much longer that I suspect that there is some kind of character data conversion going on or something along those lines. I suspect that the performance difference also may explain the problem I'm seeing (stack overflow issue.) I will try to make a test case, but I'd be interested in your thoughts on that.
-Mark
Posted: Wed 21 Jul 2010 23:40
by MarkF
I think I've got it. It looks like the problem is related to empty clobs and sorting. If you use the following in place of your code that generates the data:
Code: Select all
MySession.ExecSQL('insert into test_table values (1, ''1 Clob has data'', ''Clob data.'')', []);
MySession.ExecSQL('insert into test_table values (2, ''2 Clob is empty'', empty_clob())', []);
MySession.ExecSQL('insert into test_table values (3, ''3 Clob is null'', '''')', []);
MySession.Commit;
Then open with select * from test_table and try the sort. It immediately throws the stack overflow error along with a few access violations as well. Please let me know if that doesn't do it for you and I'll create a full test case. It would also be great if the performance issue was looked at.
Thanks,
-Mark
Posted: Fri 23 Jul 2010 13:50
by bork
Hello
Thank you for the information. We have reproduced this problem (performance & access violations) and fixed it. This fix will be included in the next ODAC build.
Posted: Fri 23 Jul 2010 20:47
by MarkF
Fantastic! Thanks for your help!
-Mark