跳到正文
Simon Willison·· 14 天前AI 評分39

`parse_number()` SQLite 擴充功能:C 與 Rust 比較

parse_number() as a SQLite extension: C vs Rust

AI 導讀

四種以 C 和 Rust 編寫的 SQLite 擴充功能實作相同的 `parse_number()` 行為,並測試類型保留、解析規則和浮點數準確度。最佳化 C 版本每列耗時 16–19 ns,約為 Rust 版本的兩倍速度,亦遠快於 SQL `replace()`;所有版本在 8 million 次模糊測試中結果一致。

正文

Research parse_number() as a SQLite extension: C vs Rust — Four SQLite extensions implement the same `parse_number()` behavior in C and Rust, with tests covering type preservation, parsing rules, and floating-point accuracy. The optimized C implementation was fastest at 16–19 ns per row, about twice as fast as the Rust versions and far faster than SQL `replace()`. Plain C was slower on decimals because it relied on `strtod()`, while a compact fast path closed that gap; all versions matched across 8 million fuzz checks.

I was out walking the dog when I got curious about how easy/difficult it would be to write a SQLite C extension that adds a parse_number() function for turning strings like -$1,234.56 or '12.5%' into numeric values that could be summed and sorted.

I fired up regular Claude chat (Opus 5.5) and prompted:

I'm thinking about a SQLite custom function 'parse_number(value)' that strips ',', '$', '£' and '%', handles a leading '-', and returns 'NULL' for anything it can’t parse

Explore the difference between building this as a SQLite extension in C v.s. in Rust - I want to compare performance and code complexity and size of the resulting.so(or whatever) file

This gave me a solid starting point - the Rust extension was 321 KB (using Rusqlite) and 298 KB (using sqlite-loadable-rs), while the C extension was 14 KB. But the Rust extension was faster!

Turns out that's because the Rust equivalent of C's strtod is "twice as fast". So I prompted:

Try writing your own C function for parsing text into a decimal number that catches up to or beats the Rust implementation

And it did:

Net cost per row over 2 million rows, lower is better:

first C new C rusqlite sqlite-loadable
Mixed data 67 ns 19 ns 46 ns 41 ns
Integers only 25 ns 16 ns 36 ns 32 ns
Decimals only 106 ns 18 ns 55 ns 47 ns

Finally, I had it rewrite the prototype as a research report to match the others in simonw/research by prompting this:

Clone simonw/research from GitHub and turn your above investigation into a research report as seen in that repository - you can give me a zip file to download that contains your new folder

來源:Simon Willison · simonwillison.net