Databases
StackOverflow2013
Postsdbo
POWhy fetching data from sqlite block Node.JS?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy fetching data from sqlite block Node.JS?
    text
    copied!<p>I want to fetch and export to csv file huge amount (5 - 12 milions rows) of archive data from Sqlite database. While doing this the whole server is blocked. No other connection can be handled by server (for example I couldn't open website in another tab in browser). </p> <p>Node.JS server part:</p> <pre><code>function exportArchiveData(response, query){ response.setHeader('Content-type', 'text/csv'); response.setHeader('Content-disposition', 'attachment; filename=archive.csv'); db.fetchAllArchiveData( query.ID, function(error, data){ if(!error) response.write(data.A + ';' + data.B + ';' + data.C + '\n'); }, function(error, retrievedRows){ response.end(); }); }; </code></pre> <p>Sqlite DB module:</p> <pre><code> module.exports.SS.prototype.fetchAllArchiveData = function ( a, callback, complete) { var self = this; // self.sensorSqliteDb.all( self.sensorSqliteDb.each( 'SELECT A, B, C '+ 'FROM AD WHERE '+ ' A="' + a + '"'+ ' ORDER BY C ASC' + ';' , callback, complete ); }; </code></pre> <p>I also create index on AD like CREATE INDEX IAD ON AD(A, C) and EXPLAIN QUERY PLAN show that this index is used by sqlite engine. </p> <p>Still, when I call exportArchiveData server send the data properly but no other action can be performed during this. I have a huge amount of data (5 - 12 milions of rows to send) so it takes ~3 minutes. </p> <p><strong>How can I prevent this from blocking whole server?</strong> </p> <p>I thought that if I use EACH and there will be callback's the server will be more responsive. Also Memory usage is huge (about 3GB and even more). Can I prevent this somehow? </p> <p><em>In answer to comments, I would like to add some clarifications:</em></p> <p>I use <a href="https://npmjs.org/package/sqlite3" rel="nofollow">node-sqlite3 from developmentseed</a>. It should be asynchronous and non-blocking. And it is. When statement is prepared I can request main page. But when server start serving data, then Node.js server is blocked. I guess thats because request for home page is one request to call some callback while there are milions request for callback handling archive data "EACH". </p> <p>If I use sqlite3 tool from linux command line I do not get rows immediately but that is not the problem as long as node-sqlite3 is non-blocking. </p> <p>Yes. I'm hitting CPU max. What is worse, when I request twice as much data the whole memory is used, and then server freeze forever.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload