On 2017-03-24 14:42, Gary Doades wrote:
Hi everyone,
Firstly, I realise this is an old subject, but I still can't find any
easy answer.
Really, the question is simple: Is there ANY way of TSQLQuery NOT
reading the entire result set into memory?
What about making multiple queries and setting sql's LIMIT?
Should you really be writing sql queries that return millions of
results?
Is this a design choice that could be a bad one?
For example in many web programs, I only retrieve 10-100 results back by
using SQL's LIMIT feature...
Example:
SELECT * FROM Orders LIMIT 15, 10
SQLDB likely has a similar setting/feature so that you don't have to
write actual SQL code to achieve it.
And in your case, you may actually really need to retrieve millions of
results of the database, I don't know your use case. But generally in my
web programs where I have millions of results, I use SQL to my advantage
and it's language features, such as LIMIT, instead of retrieving all of
the results at once which kind of defeats the purpose of an sql database
since it's job is to give you only the data you need. Are you using
pascal to do the work that the database could already do for you?
I could be misunderstanding your situation, and again your use case
could be different.
This is really killing me. I've got quite a lot of web code using
sqldb and it works really well. The classes for connection,
transaction, query etc. are perfect for general use and make it so
easy to write database applications. I find it excellent that I only
need to change the connection class in my code to use a different
database.
For web code (mine included), fetching a few dozen, hundred or even a
few thousand rows is no real problem. The sqldb classes are *VERY*
fast and very easy to use.
The problem comes when I need to write some apps to either analyse or
move a lot of data. Initially all I need to do is analyse some data by
simply reading through rows, doing some calculations and other
analysis. For even 1 million rows this is very fast (10 seconds using
MySQL) so no major problems. However, it does use quite a lot of
memory.
If you need a temporary work around simply use SQL Limit feature.. then
your data that comes back into your memory on your end is only what you
have limited the result set to... If you need to combine all this data
into one big data set to do analysis though, maybe sql LIMIT is the
wrong tool for the job, I don't know.
But it's a big warning sign if you have a program that retrieves
millions of results from a database and you only need to display 10 of
those items to the end user - in that case LIMIT is really useful. But,
you are analyzing these millions of results maybe monolithically so your
case may be different.
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
