Recent

Author Topic: [SOLVED]Is it possible to fetch records one by one ?  (Read 1650 times)

rvk

  • Hero Member
  • *****
  • Posts: 4327
Re: Is it possible to fetch records one by one ?
« Reply #15 on: April 26, 2020, 06:32:11 am »
In fact,  I have a file of about 150MB size to distribute to clients.
To transfer such a big file over a poor WAN connection is very time-costing, says over 10 minutes.
Yikes  :o There is your problem.
I was wondering why even one record would be slow.
But you say over WAN (internet).
You SHOULD have mentioned that from the beginning.

The problem is not the record size of 150MB (although large) but the fact you're accessing the database over the internet. Database communication protocols are inherently chatty. That means for every packet of 64K bytes there are multiple messages over the line. All are subject to a large transfer time (look at the ping-time of each message). And one record of 150Mb equals tens of thousands messages. Each with it's own 'ping'-delay.

Databases are not meant to be accessed directly over the internet. It's just to slow. And if you have your mysql server exposed to the full internet (without vpn etc)...  :o O, don't get me started about security. Just don't do it.

You are better of creating a multi-tier solution. Create a middle-man (for example a web-server) which does the actual sql query. The web-server is on the same network as the mysql-server, and only send json messages with the result back to the client. Only when you really need the actual record, then you transfer the actual data (and 150MB should transfer in 12 seconds to 2 minutes, 100Mbps resp. 10Mbps). With this solution it would even be possible to zip the resulting file before transfer. Or send multiple files in one zip. Because file transfer over the internet is much faster then 'database-protocol' (really, DB-protocol is really really chatty) you'll speed up your client considerably.

Really, databases are not meant to be accessed directly over the internet and especially not for 'file-transfer'.

kinlion

  • Jr. Member
  • **
  • Posts: 68
  • I Love Lazarus
Re: Is it possible to fetch records one by one ?
« Reply #16 on: April 26, 2020, 08:27:26 am »
Really, databases are not meant to be accessed directly over the internet and especially not for 'file-transfer'.
You are right.
My App is designed for running in LAN. But now the servrer has been moved to CLOUD :(

Thanks  :)
« Last Edit: April 26, 2020, 08:30:15 am by kinlion »
Lazarus 2.0.8 / FPC 3.0.4 / SVN 62944 / Win10x64

 

TinyPortal © 2005-2018