Fetching over 400 000 records from database via odbc php.

Posted by esemashko on 19-Jun-2014 03:52

Hello, I'm trying to fetch 22.5 millions records from DataBase via ODBC and PHP, but odbc_fetch returns only 400000 records. What I noticed also. The simple query

SELECT COUNT(*) FROM PUB.SaleGood

doesn't return any value via ODBC, but correct returns value via SQLExp.
Help me please, to resolve this issue.

OpenEdge Server - Linux Ubuntu, OpenEdge version - 10.1B02

OpenEdge General - Forum
Posted by steve pittman on 23-Jun-2014 10:05

[collapse]On 6/23/2014 8:24 AM, esemashko wrote:
Reply by esemashko

I tried to increase ArraySize in DSN, but server stopped with message:

[2014/06/23@03:03:30.562954248389133+0400] P-18855      T-1749755648 I SRV     2: (915)   Lock table overflow, increase -L on server L = 16000

Stop receiving emails on this subject.

Flag this post as spam/abuse.



You may be running with a Transaction Isolation Level of Repeatable Read, if you get 16000 locks.
Repeatable Read will lock every record read until end of transaction. You may not need this behavior.

You might try using Transaction Isolation Level of Read Committed.

The isolation level can be set in the ODBC Data Admin for the DSN. This assumes that your app and PHP do not over-ride the isolation level defined for the DSN.

If  your applications is also writing to the db via Insert/Update/Delete while reading, then the isolation level has no effect on locking for writes, and these will not be affected.

Hope this helps,        ......sjp


 [/collapse]

All Replies

Posted by Valeriy Bashkatov on 19-Jun-2014 08:24

Hello esemashko,

I think that this article will help you:

"Is there a way in SQL-92 to limit the number of records in the result set that the query returns"

knowledgebase.progress.com/.../P26439

"...When programming an ODBC client using the OpenEdge 10.1A or later ODBC driver, use the ArraySize attribute in a DSN-less connection string..."

Regards,

Valeriy

Posted by esemashko on 20-Jun-2014 02:14

I read the article, but increasing of ArraySize doesn't help me. Moreover, increasing ArraySize value more than 16000 led to the fact that server throw critical exception and stopped.

Posted by Valeriy Bashkatov on 20-Jun-2014 02:23

Related topic community.progress.com/.../10923.aspx,

but the answer was not found yet.

Maybe someone faced a similar problem?

Regards,

Valeriy

Posted by Valeriy Bashkatov on 20-Jun-2014 02:24

Can anybody of technical support can help?

Posted by pimw on 20-Jun-2014 04:05

Hi,

There is no limitation in the Connect for ODBC drivers regarding number of rows returned. I was also unable to find any related articles in out Knowledgebase. This limitation is either PHP- or application related.

To further investigate, I suggest to open a Support Ticket, see Article Number 000047073 how to do this.

Regards,

Pim

Posted by pimw on 20-Jun-2014 04:34

Hi,

There is no limitation in the Connect for ODBC drivers regarding number of rows returned. I was also unable to find any related articles in out Knowledgebase. This limitation is either PHP- or application related.

To further investigate, I suggest to open a Support Ticket, see Article Number 000047073 how to do this.

Regards,

Pim

Posted by Thomas Mercer-Hursh on 21-Jun-2014 09:20

Why a new thread on the same topic.  Lacking a response which solves the problem here, I recommend opening a support case as suggested on the original thread.  The other thing you could try is to do the select with a starting key supplied that will put you higher in the stack.  If that gets you the same number of records, it will be very clear that the problem is a property of the connection.

Posted by esemashko on 21-Jun-2014 09:20

Hello, I'm trying to fetch 22.5 millions records from DataBase via ODBC and PHP, but odbc_fetch returns only 400000 records. What I noticed also. The simple query SELECT COUNT(*) FROM PUB.SaleGood doesn't return any value via ODBC, but correct returns value via SQLExp. Help me please, to resolve this issue. OpenEdge Server - Linux Ubuntu, OpenEdge version - 10.1B02

Posted by eugenel on 21-Jun-2014 15:35

I don't think it is a PHP limitation. I can get a million rows to return with PHP without any problem using DataDirect SQL Server Wire Protocol driver. Which driver do you use? How big is a row in your table? I think it is either your app or your system memory limitation.

Good luck,

Eugene

Posted by esemashko on 23-Jun-2014 03:43

I'm using ODBC driver, odbcinst.ini contains such string:

Driver=/usr/dlc/odbc/lib/pgoe1022.so

And row consists of 11 columns:

   10 KeySaleCheck               inte      
   20 KeyDepartment             inte
   30 Code                               deci-0
   40 KeyMaterialEntity          inte
   50 KeyRange                      inte
   60 QtyCash                         inte
   70 QtyBank                          inte
   80 DiscountBank               deci-2
   90 DiscountCash              deci-2
  100 PayCash                      deci-2
  110 PayBank                       deci-2

How I can check, has the system some memory limitations or no?
And why ODBC driver not returns result for COUNT(*) request, but JDBC returns?

Thanks.

Posted by esemashko on 23-Jun-2014 07:24

I tried to increase ArraySize in DSN, but server stopped with message:

[2014/06/23@03:03:30.562954248389133+0400] P-18855      T-1749755648 I SRV     2: (915)   Lock table overflow, increase -L on server L = 16000

Posted by steve pittman on 23-Jun-2014 10:05

[collapse]On 6/23/2014 8:24 AM, esemashko wrote:
Reply by esemashko

I tried to increase ArraySize in DSN, but server stopped with message:

[2014/06/23@03:03:30.562954248389133+0400] P-18855      T-1749755648 I SRV     2: (915)   Lock table overflow, increase -L on server L = 16000

Stop receiving emails on this subject.

Flag this post as spam/abuse.



You may be running with a Transaction Isolation Level of Repeatable Read, if you get 16000 locks.
Repeatable Read will lock every record read until end of transaction. You may not need this behavior.

You might try using Transaction Isolation Level of Read Committed.

The isolation level can be set in the ODBC Data Admin for the DSN. This assumes that your app and PHP do not over-ride the isolation level defined for the DSN.

If  your applications is also writing to the db via Insert/Update/Delete while reading, then the isolation level has no effect on locking for writes, and these will not be affected.

Hope this helps,        ......sjp


 [/collapse]

Posted by eugenel on 23-Jun-2014 10:09

The sql "select COUNT(*)  from table" with huge number of rows could be just slow for the server to return. You may file a performance request to OpenEdge about this issue. Not sure how your app handles that many rows, I suggest you fetch what you need in memory, examine/analyze it, reuse the memory and fetch the next set of rows.

Best regards,

Eugene

Posted by gus on 23-Jun-2014 10:10

and:

the OpenEdge RDBMS uses a memory-resident lock table for row and table locks. The -L configuration parameter referred to in the error message is used to specify the size of the lock table.

Posted by esemashko on 24-Jun-2014 02:13

Thank you very much for help.

Posted by esemashko on 24-Jun-2014 07:14

Plase, explain me, why I recieved 400000 records, if accsess to 1 record - is 1 record in lock record table and it's size is 16000?

Thanks.

Posted by esemashko on 24-Jun-2014 07:14

Hello.

I have a table in database with more than 22500000 of records. I'm trying to fetch it by PHP and ODBC, but odbc_fetch returns only 400000 of records. Please, help me to resolve this issue.

Posted by Libor Laubacher on 24-Jun-2014 08:11

The first question would be why does someone need to fetch 22 1/2 mil records at the first place .... eg for what reason? Secondly SELECT COUNT(*) is supposed to return 1 record, so no need to change any "fetch" parameters. If the JDBC works fine and ODBC does not, there might be a problem in the ODBC driver, so upgrading to more recent version might be a solution (10.1B is kinda old), or trying to unixODBC CLI first to see if the problem's there as well. Or JDBC/ODBC bridge.

This thread is closed