Large datasets

[Mahanimann]

Hi,
I'm evaluating Xailer (so far pretty impressed) and have some questions I
hope somebody could answer:
What is the best way to open MySQL (v5.0.11) tables with +700.000 rows?
I've tried a lot of combinations (client/serverside cursors, setting max
rows=100 etc.) using TSqlTable, but that exhaust the memory of the PC. I
guess the answer is TSqlQuery with a limit in SQL SELECT statement (the
:cSelect property, the :nMaxRecords does not help on the memory problem).
So, do I have to actively use the TSqlQuery:cSelect property, or are there
any built-in functionality in Xailer wich let us seamlessly use large SQL
datasets the same way as .DBF files? (In Clipper with COMIX I can access,
browse and filter huge .DBF files without any care about how many records
there are in the table.)
How do you do it when you design for example a browse window with such large
SQL tables?
Any idea or remark about this is appreciated, thanks.
Regards,
Paal

[ignacio]

Dear Sir,
There is no best way I am afraid. On any SQL engine that huge Select
statement will surely affect perfomance on both, the server and the client.
As I have said many times in this forum, programming for SQL is completele
difference than programming for DBF's. I suggest you try to construct a QBE
(Query by example) form with a Browse.
Regards,
--
Ignacio Ortiz de Zúñiga
http://www.xailer.com
"Mahanimann" <paaldalen@gmail.com> escribió en el mensaje
news:46f6d5b7$[email=1@ozsrv2.ozlan.local...]1@ozsrv2.ozlan.local...[/email]
> Hi,
>
> I'm evaluating Xailer (so far pretty impressed) and have some questions I
> hope somebody could answer:
>
> What is the best way to open MySQL (v5.0.11) tables with +700.000 rows?
>
> I've tried a lot of combinations (client/serverside cursors, setting max
> rows=100 etc.) using TSqlTable, but that exhaust the memory of the PC. I
> guess the answer is TSqlQuery with a limit in SQL SELECT statement (the
> :cSelect property, the :nMaxRecords does not help on the memory problem).
>
> So, do I have to actively use the TSqlQuery:cSelect property, or are there
> any built-in functionality in Xailer wich let us seamlessly use large SQL
> datasets the same way as .DBF files? (In Clipper with COMIX I can access,
> browse and filter huge .DBF files without any care about how many records
> there are in the table.)
>
> How do you do it when you design for example a browse window with such
> large SQL tables?
>
> Any idea or remark about this is appreciated, thanks.
>
> Regards,
> Paal
>

[Stephan Hennekens]

Hi Paal,
Assuming you don't want to see the data in a browse you could use ADO
directly to acces the data:
For example:
oCon := CreateObject("ADODB.Connection")
oCon:Open(<connection string here>)
oRS := oCon:Execute("SELECT * FROM <table> WHERE <where statement>")
DO WHILE ! oRS:Eof
value := oRS:Fields[<field name>]:Value
// do something with the value
oRS:MoveNext()
ENDDO
Regards,
Stephan
"Mahanimann" <paaldalen@gmail.com> wrote in message
news:46f6d5b7$[email=1@ozsrv2.ozlan.local...]1@ozsrv2.ozlan.local...[/email]
> Hi,
>
> I'm evaluating Xailer (so far pretty impressed) and have some questions I
> hope somebody could answer:
>
> What is the best way to open MySQL (v5.0.11) tables with +700.000 rows?
>
> I've tried a lot of combinations (client/serverside cursors, setting max
> rows=100 etc.) using TSqlTable, but that exhaust the memory of the PC. I
> guess the answer is TSqlQuery with a limit in SQL SELECT statement (the
> :cSelect property, the :nMaxRecords does not help on the memory problem).
>
> So, do I have to actively use the TSqlQuery:cSelect property, or are there
> any built-in functionality in Xailer wich let us seamlessly use large SQL
> datasets the same way as .DBF files? (In Clipper with COMIX I can access,
> browse and filter huge .DBF files without any care about how many records
> there are in the table.)
>
> How do you do it when you design for example a browse window with such
> large SQL tables?
>
> Any idea or remark about this is appreciated, thanks.
>
> Regards,
> Paal
>

[Mahanimann]

Hi Stephan,
> Assuming you don't want to see the data in a browse you could use ADO
> directly to acces the data:
> For example:
> oCon := CreateObject("ADODB.Connection")
> oCon:Open(<connection string here>)
> oRS := oCon:Execute("SELECT * FROM <table> WHERE <where statement>")
Is this different from using a TMySqlDataSource with TSqlQuery?
Thanks,
Paal

[Stephan Hennekens]

Yes, it's different. It's worth trying. However, if you want to display data
in a grid than you should use the Xailer controls. But 700.000 records in a
grid doesn't make sense!
Regards,
Stephan
"Mahanimann" <paaldalen@gmail.com> wrote in message
news:46f7967c$[email=1@ozsrv2.ozlan.local...]1@ozsrv2.ozlan.local...[/email]
> Hi Stephan,
>
>> Assuming you don't want to see the data in a browse you could use ADO
>> directly to acces the data:
>> For example:
>> oCon := CreateObject("ADODB.Connection")
>> oCon:Open(<connection string here>)
>> oRS := oCon:Execute("SELECT * FROM <table> WHERE <where statement>")
>
> Is this different from using a TMySqlDataSource with TSqlQuery?
>
> Thanks,
> Paal
>
>
>

[Mahanimann]

> Yes, it's different. It's worth trying. However, if you want to display
> data in a grid than you should use the Xailer controls. But 700.000
> records in a grid doesn't make sense!
No, not with SQL, but with Clipper/COMIX it doesn't matter. I'm thinking
about how to make the user interfaces with SQL approach. But before I do
anything about that I'm playing with ADS to see if I get the Clipper/COMIX
effect from it. (i.e. don't care about how many rows/records there are in
the dataset.)
Thanks for your reply,
Paal

[Mahanimann]

> There is no best way I am afraid. ... As I have said many times in this
> forum, programming for SQL is completele difference than programming for
> DBF's. I suggest you try to construct a QBE (Query by example) form with a
> Browse.
Ok.
Would you say it is possible to get the Clipper/COMIX effect (i.e. it
doesn't matter how many rows/records there are in the dataset) with ADS,
preferably using a dictionary with .ADT tables?
Thanks,
Paal