handling 2.550.800 rows and returning table?

Rocojo · Post by **Rocojo** » Mon 20 Feb 2006 10:52

I've builded a .dll (C++ builder and MyDAC 40028) witch is acting as a communicationlayer between my software and the databaseserver.

I still have some strange issues:

- handling 2.550.800 rows

Handling 2.550.800 rows
As you see i have a lot of rows.... and there will be more, much more.

What do i want?
First i builded a Get function. This function is the external function for my software (not for the driver) to get information out of the MySQL databases and Tables. My driver is executing a select function and after this i want to return the results to my software.

The issue
I've tried to use MyTable for this, but when i set: "MyTable->Active->true" I have to wait a very long time for the results..... FetchAll doesn't matter if it is true or false and doing a select before with a limit also.

What is the best way to solve my issue? I thought about an internal table in the driver and fill this with the results of my query and pass this as result, but how can i pass a table??

Maybe i can return a bool which is saying if there are more then one results and then the program can get the next result by building a function NextRow... or something.

Does somebody have a nice idea about this?[/u]

Antaeus · Post by **Antaeus** » Mon 20 Feb 2006 13:30

> FetchAll doesn't matter if it is true or false and doing a select before with a limit also.
It may happen because you use ORDER BY clause or any other conditionals that need to process all the records in the table.

>What is the best way to solve my issue?
Try to change your query.

GEswin · Post by **GEswin** » Mon 20 Feb 2006 21:37

As Antaeus says, try to query only what's needed. If you really need to query that much, i would approach to a solution using the LIMIT clause to fetch for example from 10.000 records at a time, when finish processing get next 10.000... Also think that holding that much records in memory can make your computer get crazy swapping to disk.

( Reference for LIMIT clause is in http://dev.mysql.com/doc/refman/5.0/en/select.html )

Regards

Rocojo · Post by **Rocojo** » Fri 24 Feb 2006 13:13

About memory i think you are right... using Limit is the best solution in combination of a next command, i think

It's working now by putting everything in an internal table.......(memory gets almost full) and working with a next command.

so now i am going to try to work with limit command between the driver and the MySQL server. I have to test some things to know how it will react.

thank you for your help

GEswin · Post by **GEswin** » Fri 24 Feb 2006 21:23

ok, please share results at forum

Rocojo · Post by **Rocojo** » Tue 28 Feb 2006 15:12

ok this is what i builded now:

First i have a function in the DLL called GetSignals ( ........, int limitmin, limitmax)

this returns a stringlist with all the rowid's

then i builded a function where you can get a single signal
GetSignal ( int rowid )

this returns a stringslist with the single row and its columns.

now i started a new topic in the forum because the memory is getting ful....