On 10 September 2015 at 22:09, David Rhys Jones <[email protected]> wrote: > Database performance, high critical databases it's faster to write text than > binary, this might have changed with the last version of SQL but it was true > up until '2008
Binary data is naturally faster to copy and compare than text because processing text must always consider encodings and collations, and sometimes line endings too, whereas binary data is copied with memcpy and compared with memcmp. If binary values are slower in a particular DBMS, then that implementation has added some artificial costs to using binary data (for example, by allowing varchar but not varbinary to be stored directly in the leaves of the b-tree). Base-64-encoding a value also increases its storage size by 33%. That is a significant overhead that reaches beyond the DBMS, with equally significant potential to affect many performance metrics (e.g. more CPU cache misses, more OS buffer cache misses, more page faults, more I/O everywhere, higher infrastructure costs). > it's more secure to store as base64, as you don't get problems trying to get > the data back out. Just that what you put in is what you are getting out, > without strange driver problems or Collation issues. Base-64-encoded values are *text*. If you store base-64-encoded values inside a database, you are actually introducing an additional risk of text-encoding and collation problems when there previously was none. In my view, there is never a good reason to store base-64-encoded values in a database system that has a varbinary (or equivalent) data type. -- Thomas Koster
