Page 1 of 1

Zeos fails to open huge recordsets from MSSQL 2000

Posted: 26.02.2007, 00:56
by mmbom
Guys,
I'm trying to open a huge recordset (over 3,000,000 rows) which is basically plain text documents, but Zeos TZDBLibStatement fails because it tries to create a cached recordset, and there is not enough memory on 32bit system to do that.
I know that Zeos do that because dblib can’t execute any other sql until it closes a fetching result.
I can’t divide my sql result because it takes to long to generate this sql.
Is possible to make the TZDBLibStatement only generates this cached recordset on demand (i.e.: on a new sql requisition, or a non forward only recordset)?
With best regards,
Adriano.

Posted: 26.02.2007, 09:41
by mdaems
Hi,

It may be possible to make this, but it would require a special treatement of dblib in zeoslib. The zeoslib dblib driver would have to block all functions requiring sql to be executed while still fetching new result rows. This is a huge handicap while using the components, I think. I'm not sure it can work that way, anyway.

Apart from this: why would you want to have all this data processed by your application in one single query? Usually there are less heavy solutions to this problems. (eg. doing some work on the server, but for a plain export of data that doesn't work, I know)

Mark

Posted: 26.02.2007, 15:34
by mmbom
Mark,
I'm working with text indexation. A single document itself is very small (less than 2k). Without the fetching cache, it is not a heavy operation, even to the server.
I could make it using a cursor, the only problem is 3 thousands “FETCH NEXT” is a huge bottleneck.
Another problem is, my application must be cross-server, and works with oracle and mysql, so I can’t use some server specific feature.
I’m willing to help you guys, to make it possible, because I will have to make it anyway.

Regards,
Adriano.

Posted: 18.03.2009, 17:55
by arnaldofranco
Hi Forum, I Have a like problem.
The scenario is Delphi 7, ZeosLib 6.6.4 stable, MSSqlServer 2005.
I have a table with 3500 records where two fields are blob fields. The DB size is 1.6 GB; mainly ocupied by such table.
I connect a TZTable component to that table and when open OOPS!! server dead for 20 minutes. I Think dataset (3500 regs) travel fully to client aplication. Isn`t?
Is there any way TzTable retrieve one recordt at time and no cache all table from server?.
or anybody Knowns the trick to read one record at time and get on demand to browse it smarty.?
or is there any manner to limit the amount of rows that TzTable could retrieve at time?
or FetchOnDemand property?

Thanks For Help

PD I need store the images in DB.
When I was using BDE this works fine; but i don`t like ODBC anymore. Need make deploy of aplication so simple as possible.

is there a solution on this issue yet ?

Posted: 01.12.2009, 15:44
by arthurhabraken
I'm running into the same problem. I'd like to use a grid for a table with more than 11 million records. Besides the fact that there is always a way to prevent an out-of-memory error on opening huge tables, I just like to use a DBGrid and don't understand why the whole table is read into memory, or how we can prevent to read the whole table into memory.

I dare to say that I'm pretty experienced in Delphi, and I would really like to implement a working version of the 'fetchrow'-property that has been implemented by someone also in this forum. But I do need some first help how to start.

Posted: 02.12.2009, 22:32
by mdaems
Arthur,

Feel free to think about this problem. If you want help to get started, just contact me by IM or using a private message. (When in private we can use dutch ;) )

Mark