4 ms·

Do you have performance numbers, especially in comparison to json1? Wondering how much benefit you get from a binary encoding scheme.

by tylerl 8y ago

rgovostes 8y ago
I don’t, sorry. I did not build this with performance in mind, but for querying packet captures of a protocol built on Protobuf. Any query is going to require deserializing every row in the table (JSON and Protobuf alike) which is basically a non-starter for any project with performance requirements. And I believe that Protobuf messages are decoded as a whole, rather than just the desired field, which is going to be slower.
- scottlamb 8y ago
  Interesting library, thanks! > Any query is going to require deserializing every row in the table (JSON and Protobuf alike) which is basically a non-starter for any project with performance requirements. Apparently you can create indexes on the json functions, so I assume on your proto ones too. > I believe that Protobuf messages are decoded as a whole, rather than just the desired field, which is going to be slower. With the official library, yes, but it's an implementation/API choice. The wire format [1] can be skimmed fairly efficiently. Most significantly, message/byte/string fields all are written as tag number, length, data. So if there are submessages (entire trees) you don't care about, you can just skip over them. I've seen custom proto decoding things do this. I think the most efficient approach would be to parse the path once per SQL query into a tag number-based path. And then have the per-row function just follow those with custom decoding logic. Unfortunately from my quick skim, it looks like SQLite's extension API doesn't really support this. You'd want it to build some context object for a given path/proto, then call extract with it a bunch of times, then tear it down. Still, I suppose you could do a LRU cache of these or something. btw, I see your code is compiling a regex [edit: originally wrote proto by mistake] in the per-row path. [2] I haven't profiled, but I'd bet that's slowing you down a fair bit. You could just make it a 'static const std::regex* kPathElementRegexp = new std::regex("...")' to avoid this. (static initialization is thread-safe in C++. The heap allocation is because it's good practice to ensure non-POD globals are never destructed. Alternatively, there's absl::NoDestructor for this.) [1] https://developers.google.com/protocol-buffers/docs/encoding https://developers.google.com/protocol-buffers/docs/encoding [2] https://github.com/rgov/sqlite_protobuf/blob/0a148ac6a5a2c02ec4f9a80cad5f4ee51175ff5a/src/protobuf_extract.cpp#L113 https://github.com/rgov/sqlite_protobuf/blob/0a148ac6a5a2c02...
  - rgovostes 8y ago
    You mean it's recompiling the regex on every pass? Ah, yeah, I hadn't thought about that. Thanks for the tip.

Do you have performance numbers, especially in comparison to json1? Wondering how much benefit you get from a binary encoding scheme.

by tylerl 8y ago

Do you have performance numbers, especially in comparison to json1? Wondering how much benefit you get from a binary encoding scheme.

rgovostes 8y ago

I don’t, sorry. I did not build this with performance in mind, but for querying packet captures of a protocol built on Protobuf. Any query is going to require deserializing every row in the table (JSON and Protobuf alike) which is basically a non-starter for any project with performance requirements. And I believe that Protobuf messages are decoded as a whole, rather than just the desired field, which is going to be slower.

scottlamb 8y ago
Interesting library, thanks! > Any query is going to require deserializing every row in the table (JSON and Protobuf alike) which is basically a non-starter for any project with performance requirements. Apparently you can create indexes on the json functions, so I assume on your proto ones too. > I believe that Protobuf messages are decoded as a whole, rather than just the desired field, which is going to be slower. With the official library, yes, but it's an implementation/API choice. The wire format [1] can be skimmed fairly efficiently. Most significantly, message/byte/string fields all are written as tag number, length, data. So if there are submessages (entire trees) you don't care about, you can just skip over them. I've seen custom proto decoding things do this. I think the most efficient approach would be to parse the path once per SQL query into a tag number-based path. And then have the per-row function just follow those with custom decoding logic. Unfortunately from my quick skim, it looks like SQLite's extension API doesn't really support this. You'd want it to build some context object for a given path/proto, then call extract with it a bunch of times, then tear it down. Still, I suppose you could do a LRU cache of these or something. btw, I see your code is compiling a regex [edit: originally wrote proto by mistake] in the per-row path. [2] I haven't profiled, but I'd bet that's slowing you down a fair bit. You could just make it a 'static const std::regex* kPathElementRegexp = new std::regex("...")' to avoid this. (static initialization is thread-safe in C++. The heap allocation is because it's good practice to ensure non-POD globals are never destructed. Alternatively, there's absl::NoDestructor for this.) [1] https://developers.google.com/protocol-buffers/docs/encoding https://developers.google.com/protocol-buffers/docs/encoding [2] https://github.com/rgov/sqlite_protobuf/blob/0a148ac6a5a2c02ec4f9a80cad5f4ee51175ff5a/src/protobuf_extract.cpp#L113 https://github.com/rgov/sqlite_protobuf/blob/0a148ac6a5a2c02...
- rgovostes 8y ago
  You mean it's recompiling the regex on every pass? Ah, yeah, I hadn't thought about that. Thanks for the tip.