On Wed, 21 Mar 2007, Marc Cousin wrote:
> I think I haven't explained the memory issue correctly :
I realise it's an issue for large selects, but in the case given:
>
> The example Kern gave is :
>
> "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
> "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
> "WHERE Job.JobId IS NULL LIMIT 300000";
>
> and it only fails if I remove the "LIMIT 3000000".
Would be better solved with:
"SELECT COUNT(*) FROM JobMedia "
"LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
"WHERE Job.JobId IS NULL ";
Because the very next lines in dbcheck simply count the resulting lines of
output.
Similarly the deletion routine can be altered from the Kern's example,
plus a loop using DELETE statements, to:
"DELETE FROM Job "
"LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
"WHERE Job.JobId IS NULL ";
> The problem you mention is that you select 300,000 records from the database.
> What happens then is that your SQL CLIENT process will allocate all these
> records in memory (except if you use a cursor, but that's not the question).
>
> As a consequence, if you remove the LIMIT code, the client will have to store
> millions of records in memory (for you to use them). The server process won't
> have to.
This is exactly what I was seeing with dbcheck.
Why have a dog and then do all the barking yourself?
In this case the dog is the SQL database and the barking is the needless
extraction and [counting|deleting] of individual NULL JobIds
The comments about SQL crashes are because I have seen this happen as
databases grow HUGE. Mine is somewhat in excess of 250 million entries.
AB
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
