Queries Compression Comparisons
Section
{:ok, user} = Sanbase.Accounts.User.by_email("ivan.i@santiment.net")
sql = """
SELECT *
FROM eth_transfers
LIMIT 20000
"""
params = %{}
{:ok, query} =
Sanbase.Queries.create_query(
%{name: "Test Query", sql_query_text: sql, sql_query_parameters: params},
user.id
)
query_metadata = Sanbase.Queries.QueryMetadata.from_local_dev(user.id)
{:ok, data} = Sanbase.Queries.run_query(query, user, query_metadata)
result = data
mb_size = fn x ->
x |> byte_size() |> Kernel./(1024 * 1024) |> Float.round(2)
end
alias Sanbase.Queries.Executor.Result
# Compute Query Sizes with different types of compression
query_size_no_compression_mb = :erlang.term_to_binary(result) |> Base.encode64() |> then(mb_size)
IO.puts("Query Result size without compression: #{query_size_no_compression_mb} MB")
query_compressed_rows =
result
|> Map.put(:compressed_rows, Result.compress_rows(data.rows))
|> Map.put(:rows, nil)
query_size_compression_mb =
:erlang.term_to_binary(query_compressed_rows) |> Base.encode64() |> then(mb_size)
IO.puts("Query Result size with rows compression: #{query_size_compression_mb} MB")
query_size_compress_whole_query_mb =
:erlang.term_to_binary(result) |> :zlib.gzip() |> Base.encode64() |> then(mb_size)
IO.puts("Query Result size with gzip whole result: #{query_size_compress_whole_query_mb} MB")
query_size_double_compression_mb =
:erlang.term_to_binary(query_compressed_rows)
|> :zlib.gzip()
|> Base.encode64()
|> then(mb_size)
IO.puts("Query Result size double compression result: #{query_size_double_compression_mb} MB")
a =
result
|> Map.from_struct()
|> Jason.encode!()
|> :zlib.gzip()
|> Base.encode64()
|> then(mb_size)
IO.puts("gzip: #{a}")
b =
result
|> Map.from_struct()
|> Jason.encode!()
|> :zlib.zip()
|> Base.encode64()
|> then(mb_size)
IO.puts("zip: #{b}")
c =
result
|> Map.from_struct()
|> Jason.encode!()
|> :zlib.compress()
|> Base.encode64()
|> then(mb_size)
IO.puts("compress: #{c}")
result
defmodule Compressor do
def compress_encode_result(query_result) do
query_result
|> Map.from_struct()
|> :erlang.term_to_binary()
|> :zlib.gzip()
|> Base.encode64()
end
def decode_decompress_result(result) do
result
|> Base.decode64!()
|> :zlib.gunzip()
|> :erlang.binary_to_term()
|> then(&struct!(Result, &1))
end
end
compressed = Compressor.compress_encode_result(result)
result2 = Compressor.decode_decompress_result(compressed)
result
|> Map.from_struct()
|> Enum.filter(fn {k, v} ->
v != Map.from_struct(result2)[k]
end)
m =
result
|> Map.from_struct()
|> Jason.encode!()
|> :zlib.gzip()
|> Base.encode64()
|> Base.decode64!()
|> :zlib.gunzip()
|> Jason.decode!()
|> Sanbase.MapUtils.atomize_keys_shallow()