Using data compression in communication between application and database?

Question

Hi there,

I have upcoming project where we need application to send data on compressed into database and to other application. The compression can use algorithm such as GZIP or ZLIB.

Can somebody point me some help, especially for VCL component (or ActiveX) that can compress data traffic between communicating application and database?

Just thinking about chatting or IM application but use compression between each data traffic.

My Environment:
- Delphi 7 or BDS 2006
- will use Indy for communicating between application
- will use ADO for communicating application into database
- TCP/IP or HTTP will be used as protocol

Thanks,
Dels

Answer 1

You could use any compression algorithm to compress bytes or a stream if you use Indy. Like the build in ZLib compression. That is because you control both sides of the communication. That is different when connecting to a database. You can only compress if the database server understands the compression. Usually you don't compress but let it be handled by the database drivers you are using. So that depends on the options they are offering.

http://www.swissdelphicenter.ch/torry/showcode.php?id=1617 is an example on how to compress a filestream with Delphi's ZLib. Compressing a stream to send with Indy is roughly the same.

Answer 2

I'm not aware of any way to compress data during transmission and have the database automatically understand and decompress it on the other end just to save on bandwidth, if that's what you're talking about. And I'd only recommend compressing data to send to your database if it's a large BLOB or block of text. If so, just compress it like Lars mentioned and store the compressed result as a BLOB field. But for ordinary data, it's best to leave it decompressed and in normal SQL format. That way your database can optimize its storage for speed and index it properly.