Tedious: Pause Query Results

[chacent]

when lot of rows will be received,i want to pause when sometime ,and then resume ,so that memory can in ctrl

[bretcope]

Until actual streaming is implemented (don't hold your breath), this simply isn't going to happen. Sorry.

[bretcope]

Actually, I take that back. We could probably make it work by allowing you to pause the underlying stream. I'll think about it. No promises.

[tracker1]

+1

[francesconero]

+1

[sarus]

Is there anyway to do this in the latest Tedious which appears to use streams? Can it be implemented using cursors with the existing release?

[otopsy]

:+1:

[jlaneve]

@bretcope any updates on this?

[bretcope]

@JulianLaNeve, @arthurschreiber would be the one to talk to about it at this point. I'm not very involved in Tedious these days.

[chdh]

In the past 3 years, there have been many requests for the missing pause/resume/back-pressure functionality in Tedious:

149, #186, #257, #271

And there have been similar requests for the upper level driver node-mssql:
patriksimek/node-mssql#67, patriksimek/node-mssql#225, patriksimek/node-mssql#363

Back in 2015, the expectation was that #225, which uses Dissolve to parse the TDS stream, would solve the problem. But then #225 was replaced by #285 (Generators) and that was replaced by #314 (Callbacks).

What is the current state of this problem? I am considering to move from Java and C# to Node.js/TypeScript, not only for web applications, but also for utility programs that import/export/transfer large database tables. Without control over the flow of incoming data from the database, I cannot make this technological transition. There may be work-arounds like using OFFSET/FETCH in SQL, but they are too inconvenient. We need a way to apply back-pressure and control the flow of data from a query result.

In the old days, this feature was taken for granted. In Cobol, PL/1, Visual Basic, PHP, Java, C#, ... this was never a problem. ODBC, DAO, ADO, OLE-DB, etc. all have this of course. For example in MS-Access with VBA, you can open a forward-only recordset for a large table, read it record by record, and send the data slowly to an Excel sheet or another (slower) database, without having to worry that the memory would fill up in the background. Why isn't this possible nowadays with Node.js and SQL Server? Is it too early to adopt this technology?

[chdh]

I have analyzed the data flow of the query result data:

Bytes:
  Socket.pipe(MessageIO.ReadablePacketStream)
  MessageIO.ReadablePacketStream/Transform.write()
  MessageIO.ReadablePacketStream._transform()
Packets:
  MessageIO.ReadablePacketStream/Transform.push()
  MessageIO.packetStream.on('data')
Messages:
  MessageIO.emit('data')
  Connection.messageIo.on('data')
  Connection.dispatchEvent()
  Connection.state.events['data'].apply()
  Connection.STATE.SENT_CLIENT_REQUEST.events.data()
  Connection.sendDataToTokenStreamParser()
  TokenStreamParser.addBuffer()
  StreamParser/Transform.write()
  StreamParser._transform()
  StreamParser.parseTokens()
  (row-token-parser)
Tokens:
  StreamParser/Transform.push()
  TokenStreamParser.parser.on('data')
  TokenStreamParser.emit(token.event)
Rows:
  Connection.tokenStreamParser.on('row')
  Connection.request.emit('row')
  Request.emit('row')

There are 3 points where the data flow could be temporarily suspended and back pressure could be applied:

1. Return false from MessageIO.ReadablePacketStream.write().
   When write() returns false, the pipe logic calls Socket.pause() and the data transfer in the pipe stops.
   To resume, a 'drain' event must be emitted by ReadablePacketStream.
2. Call MessageIO.ReadablePacketStream.pause().
   ReadablePacketStream would stop emitting 'data' events and the internal buffer of the transform would fill up with packets. When the high water mark is reached, this should apply back pressure to the socket pipe.
   To resume, ReadablePacketStream.resume() must be called.
3. Call StreamParser.pause().
   The StreamParser would stop emitting 'data' events and the internal buffer of the transform would fill up with tokens. This would not apply any back pressure, but it would stop the flow of the remaining tokens that are parsed from the current message.
   To resume, StreamParser.resume() must be called.

All 3 mechanisms could be used at once within a new Request.pause() method.

[tracker1]

Tangentially related, I haven't dug in... but is this feature implemented in dealing with cursors with the .Net driver?[1] As that source is available.

As for any option that doesn't provide back-pressure to the SQL stream, that would be a problem, as memory would fill up, and the node process will blow up, which is already an issue.

[1] https://github.com/dotnet/corefx/tree/master/src/System.Data.SqlClient/src/System/Data/SqlClient

[chdh]

518 implements pause/resume