------=_NextPart_000_0016_01C4FF08.F814CAF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi, yesterday i wrote this help request :
------------------------------------------------------------ -------------=
-----------------------------------------------
Hi, i have a problem with a large Delphi application that use a MySQL =
database.
There are 2 big tables that contains about 250.000 rows.
When i load the project and set to True the flag Active of the table, =
the Delphi process start to increase memory usage ... it seems that =
Delphi cache in memory the content of the table.
At end, the memory consumption by Delphi is about the disk size of the =
table (about 230Mb).

I have try the same with a large Access database table, and the problem =
don't repeat, all is ok, and thus i presume that the trouble is caused =
by MyODBC.

I'm using Delphi 4, MySQL 4.1.7 and MyODBC 3.51.10 . In MyODBC Data =
Source i set to True the flags [Don't Optimize Column Width] and [Return =
Matching Rows], as suggested by MySQL documentation.
I tryed to check the flags [Don't Cache Result] and [Force Use Of =
Forward Only Cursor], but the Delphi crash and exit without any message.

I hope that someone can help me to solve the problem.
Thanks in advance.
------------------------------------------------------------ -------------=
-----------------------------------------------

and Fredrick Bartlett quickly answer me:
------------------------------------------------------------ -------------=
-----------------------------------------------
No, no, no. Don't use TTable use TQuery and only return what you need =
from
SQL selection criteria.
------------------------------------------------------------ -------------=
-----------------------------------------------
.... many thanks to mr. Bartlett for the answer.
I've tried a simple TQuery object, but when i need to access at the =
entire content of the database table (select * from MyTable) the memory =
consumption is the same, very much.
Another reason for using TTable is that the application is very large, =
and already use many TTable object ... the work needed for changing all =
the TTable object in TQuery (and the correlated code) is very high ... =
is there another solution ? ... thanks

Aurelio (am [at] netixitalia.it)





------=_NextPart_000_0016_01C4FF08.F814CAF0--

Do you really need to fetch all that data? And all the fields? Using
SELECT * is bad practise, and I don't know how you could
present 250000 rows to a user in a meaningful manner.

Why not just select a sensible amount each time...
To get the 1st 100 rows:
SELECT field1, field2 FROM table LIMIT 0, 100

The the next 100 rows:
SELECT field1, field2 FROM table LIMIT 100, 100

Is that any help?

aurelio [at] netixitalia.it wrote:

>Hi, yesterday i wrote this help request :
>----------------------------------------------------------- ------------------------------------------------------------ -
>Hi, i have a problem with a large Delphi application that use a MySQL database.
>There are 2 big tables that contains about 250.000 rows.
>When i load the project and set to True the flag Active of the table, the Delphi process start to increase memory usage ... it seems that Delphi cache in memory the content of the table.
>At end, the memory consumption by Delphi is about the disk size of the table (about 230Mb).
>
>I have try the same with a large Access database table, and the problem don't repeat, all is ok, and thus i presume that the trouble is caused by MyODBC.
>
>I'm using Delphi 4, MySQL 4.1.7 and MyODBC 3.51.10 . In MyODBC Data Source i set to True the flags [Don't Optimize Column Width] and [Return Matching Rows], as suggested by MySQL documentation.
>I tryed to check the flags [Don't Cache Result] and [Force Use Of Forward Only Cursor], but the Delphi crash and exit without any message.
>
>I hope that someone can help me to solve the problem.
>Thanks in advance.
>----------------------------------------------------------- ------------------------------------------------------------ -
>
> and Fredrick Bartlett quickly answer me:
>----------------------------------------------------------- ------------------------------------------------------------ -
> No, no, no. Don't use TTable use TQuery and only return what you need from
>SQL selection criteria.
>----------------------------------------------------------- ------------------------------------------------------------ -
>... many thanks to mr. Bartlett for the answer.
>I've tried a simple TQuery object, but when i need to access at the entire content of the database table (select * from MyTable) the memory consumption is the same, very much.
>Another reason for using TTable is that the application is very large, and already use many TTable object ... the work needed for changing all the TTable object in TQuery (and the correlated code) is very high ... is there another solution ? ... thanks
>
>Aurelio (am [at] netixitalia.it)
>
>
>
>
>
>
>



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc [at] m.gmane.org

I'm am sorry to say that you have a design issue. When working with SQL
Databases you need to move the work to the server. Never return the whole
table. I understand your problem having worked in the past with desktop
databases (Access, Paradox etc). Don't use "Filter" in TTable use "WHERE="
in TQuery. Sorry there is no other solution to your performance issue.

Fredrick
----- Original Message -----
From: <aurelio [at] netixitalia.it>
To: <myodbc [at] lists.mysql.com>
Sent: Thursday, January 20, 2005 6:59 AM
Subject: Delphi 4 large memory usage (two)


Hi, yesterday i wrote this help request :
------------------------------------------------------------ ----------------
--------------------------------------------
Hi, i have a problem with a large Delphi application that use a MySQL
database.
There are 2 big tables that contains about 250.000 rows.
When i load the project and set to True the flag Active of the table, the
Delphi process start to increase memory usage ... it seems that Delphi cache
in memory the content of the table.
At end, the memory consumption by Delphi is about the disk size of the table
(about 230Mb).

I have try the same with a large Access database table, and the problem
don't repeat, all is ok, and thus i presume that the trouble is caused by
MyODBC.

I'm using Delphi 4, MySQL 4.1.7 and MyODBC 3.51.10 . In MyODBC Data Source i
set to True the flags [Don't Optimize Column Width] and [Return Matching
Rows], as suggested by MySQL documentation.
I tryed to check the flags [Don't Cache Result] and [Force Use Of Forward
Only Cursor], but the Delphi crash and exit without any message.

I hope that someone can help me to solve the problem.
Thanks in advance.
------------------------------------------------------------ ----------------
--------------------------------------------

and Fredrick Bartlett quickly answer me:
------------------------------------------------------------ ----------------
--------------------------------------------
No, no, no. Don't use TTable use TQuery and only return what you need from
SQL selection criteria.
------------------------------------------------------------ ----------------
--------------------------------------------
.... many thanks to mr. Bartlett for the answer.
I've tried a simple TQuery object, but when i need to access at the entire
content of the database table (select * from MyTable) the memory consumption
is the same, very much.
Another reason for using TTable is that the application is very large, and
already use many TTable object ... the work needed for changing all the
TTable object in TQuery (and the correlated code) is very high ... is there
another solution ? ... thanks

Aurelio (am [at] netixitalia.it)






--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc [at] m.gmane.org