Strange database toolbox behavior

조회 수: 9 (최근 30일)
David Goldsmith
David Goldsmith 2012년 8월 29일
I have a simple SQL Server query--SELECT [field list] FROM [in-database view]--that, with a where clause, returns the correct results--a 252 x 17 mixed-type cellarray--without generating a java heap outofmemory error, but without a where clause, generates a java heap outof emory error. If I add code similar to that provided by "Example 3 — Import Rows Iteratively Until You Retrieve All Data" in the help doc for cursor.fetch:
function data = grabInPieces(~, curs)
% Initialize result holder and fetch size
data = {};
rowLimit = 20;
% Check for and retrieve more data
while ~strcmp(curs.Data, 'No Data')
curs = fetch(curs, rowLimit);
data(end+1:end+size(curs.Data,1),:) = curs.Data;
end
end
and have a call to this inside a catch block following the first attempt to fetch, the outofmem error is dealt with, but the result set is drastically abbreviated--down to 8 rows from an expected 320 (which is the number of rows the same query returns in SQL Server Management Studio).
Any ideas?
Thanks, OlyDLG
  댓글 수: 2
Oleg Komarov
Oleg Komarov 2012년 8월 29일
Is the code you posted, exactly what you're using? If not, post the code you're using.
Which MATLAB version do you have?
David Goldsmith
David Goldsmith 2012년 8월 29일
Yes; R2011b

댓글을 달려면 로그인하십시오.

답변 (3개)

Oleg Komarov
Oleg Komarov 2012년 8월 29일
The code looks correct, unless you're doing something that fetches most of the data (without storing it) before calling grabInPieces().
You can increase the Java Heap Memory: Java Heap Memory Preferences
  댓글 수: 2
David Goldsmith
David Goldsmith 2012년 8월 29일
"The code looks correct"
Thanks.
"unless you're doing something that fetches most of the data (without storing it) before calling grabInPieces()."
Well, quite possibly I am: as I implied (but I guess didn't say explicitly), before I call grabInPieces, I try to grab it all at once (which has always worked in the past: this problem only cropped up--in extant, heretofore working code--yesterday; the only "environmental" change I can think of is that the amount of data returned by the query may have gone up significantly, 'cause the DB is populated by others); grabInPieces is only called if the attempt to grab it all at once throws the outofmem error. I don't know how DB cursors work, but you seem to be implying that they work soemwhat like file pointers, and that the cursor isn't "rewound" to the beginning of the result set when the error is thrown. So what I'm getting is what's left of the result set after the error has been thrown; that would explain both it's consistent size across runs and pinpoint precisely how many rows I'm getting before running out of memory. Assuming this is the case, is there some way, short of clearing the cursor and starting over, to force a "rewind" to the beginning of the result set? Re-execute the query? (I'd rather keep the "try for everything, grab in pieces if fail" structure, since looping is slow and I don't think the problem will happen very often.)
"You can increase the Java Heap Memory"
Yup, already did that, to the max, and I'm still having the problem. Besides, this isn't an acceptable solution--unless there's a way to do this programmatically and to an arbitrary size--because ultimately this app has to be compiled.
Thanks for your input, it sounds promising.
Oleg Komarov
Oleg Komarov 2012년 8월 29일
편집: Oleg Komarov 2012년 8월 29일
The cursor usually works like a file pointer. However, I am unsure what should happen in case of a Java out of memory error.
I would try to avoid the out of memory by estimating the memory use per row.
You can try to query the meta info of the table with:
Curs = exec(Connection, Statement);
attributes = get(rsmd(resultset(Curs)));
and establish the amount of rows to import (keeping it prudent).
Then, what you just need is to do a first pre-fetch and keep fetching iteratively but inverting the order of the commands in the while loop:
while ~strcmp(curs.Data, 'No Data')
data(end+1:end+size(curs.Data,1),:) = curs.Data;
curs = fetch(curs, rowLimit);
end
Otherwise, you can use a try catch block (but keep the commands in the loop inverted, i.e. first store the results from the previous fetch). However, I am unsure whether the state of the cursor is preserved in the catch part.

댓글을 달려면 로그인하십시오.


David Goldsmith
David Goldsmith 2012년 8월 29일
And as suddenly as it appeared, the problem has disappeared: the "where-less" query is no longer throwing a java heap out of memory error. :/

David Goldsmith
David Goldsmith 2012년 8월 30일
And now it's back again--any ideas re: how to diagnose this?
  댓글 수: 2
Oleg Komarov
Oleg Komarov 2012년 8월 30일
Well, it could help to know what you do before importing.
Sean de Wolski
Sean de Wolski 2012년 8월 30일
What else do you have open in ML? Do you have a large *.m files open in the editor, GUIs, etc? How about the current folder, are there many files in there?
I would recommend to try closing the various desktop components you are not using in an effort to minimize your Java heap footprint to see if that helps.

댓글을 달려면 로그인하십시오.

카테고리

Help CenterFile Exchange에서 MATLAB에 대해 자세히 알아보기

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by