4 ms·

Faster than xsv? AIUI, both sqlite and xsv rely on the OS filesystem buffer cache to keep their data in memory.

burntsushi 8y ago
Most definitely, yes. If you setup your indexes correctly in a relational database, then xsv isn't going to come anywhere near performing them as efficiently. The only indexing mechanism xsv has is a record index, which lets it parallelize certain workloads across a CSV file. But it doesn't have any per-field indices like relational databases do, so any kind of join done by xsv requires building the index first in memory. xsv could grow support for field level indices, but it's more work and I don't know whether it's worth it. There's a line to straddle here, because at some point, xsv isn't going to be good enough and you should instead just use a database.
- dagenix 8y ago
  I don't doubt that sqlite will wipe the floor with xsv for certain types of analysis. I also suspect that the opposite would be true for other types of analysis. Although, you'd know much better than I would - I've used sqlite quite a bit while my only usage of xsv was to work with a 100 line CSV file. Xsv was fast, but, I'm sure most tools would be at that size. What was killer, however, is that xsv gave me a lot of tools that helped exactly what I needed to do - sqlite wouldn't have helped at all. Right tool for the right job and all. What I was more curious about was if the original comment was about a particular case where sqlite beat xsv, or if it was more handwavy. Also, the comment seemed to imply that repeated queries would be faster in sqlite than with xsv but didn't mention anything about indexes. My suspicion would be that without something that can take advantage of an index, xsv and sqlite would perform about the same doing repeated table / file scans.
  - burntsushi 8y ago
    Yeah those are all great points! I think you're right to wonder a bit. Hard to say what's what without more details!
- danso 8y ago
  As a daily user of xsv, I can't imagine why a CSV processor would need to be competing with SQLite in terms of performance. For me, xsv hits the right niche in 2 very important and frequent use-cases: 1. One-off analyses or tasks involving relatively small datasets, like trimming the output of a command-line Twitter tool into xargs to, say, unfollow everyone who hasn't tweeted this year, or isn't following me and has fewer than 100 followers. If I were attempting to do a deeper analyses, SQLite would be better (because of the query syntax), but xsv/csvkit works incredibly well for quick explorations/filtering/sorting. Here's an example using csvkit (haven't quite gotten used to all of xsv's syntax) and the t command-line tool: https://gist.github.com/dannguyen/7213b16b7de79ad9c89fc2297d0c822d https://gist.github.com/dannguyen/7213b16b7de79ad9c89fc2297d... 2. Prepping data for importing into SQLite; for very large public datasets, I like using xsv/csvkit to trim columns and filter out rows before importing them into SQLite. This is especially convenient since SQLite doesn't allow for dropping columns once a table is created. If I'm ever working with data big enough to need SQLite-like performance, then I probably need SQLite's query syntax and other features. xsv is already flexible and performant for what I need in my data pipeline.

Most definitely, yes. If you setup your indexes correctly in a relational database, then xsv isn't going to come anywhere near performing them as efficiently. The only indexing mechanism xsv has is a record index, which lets it parallelize certain workloads across a CSV file. But it doesn't have any per-field indices like relational databases do, so any kind of join done by xsv requires building the index first in memory. xsv could grow support for field level indices, but it's more work and I don't know whether it's worth it. There's a line to straddle here, because at some point, xsv isn't going to be good enough and you should instead just use a database.

dagenix 8y ago
I don't doubt that sqlite will wipe the floor with xsv for certain types of analysis. I also suspect that the opposite would be true for other types of analysis. Although, you'd know much better than I would - I've used sqlite quite a bit while my only usage of xsv was to work with a 100 line CSV file. Xsv was fast, but, I'm sure most tools would be at that size. What was killer, however, is that xsv gave me a lot of tools that helped exactly what I needed to do - sqlite wouldn't have helped at all. Right tool for the right job and all. What I was more curious about was if the original comment was about a particular case where sqlite beat xsv, or if it was more handwavy. Also, the comment seemed to imply that repeated queries would be faster in sqlite than with xsv but didn't mention anything about indexes. My suspicion would be that without something that can take advantage of an index, xsv and sqlite would perform about the same doing repeated table / file scans.
- burntsushi 8y ago
  Yeah those are all great points! I think you're right to wonder a bit. Hard to say what's what without more details!
danso 8y ago
As a daily user of xsv, I can't imagine why a CSV processor would need to be competing with SQLite in terms of performance. For me, xsv hits the right niche in 2 very important and frequent use-cases: 1. One-off analyses or tasks involving relatively small datasets, like trimming the output of a command-line Twitter tool into xargs to, say, unfollow everyone who hasn't tweeted this year, or isn't following me and has fewer than 100 followers. If I were attempting to do a deeper analyses, SQLite would be better (because of the query syntax), but xsv/csvkit works incredibly well for quick explorations/filtering/sorting. Here's an example using csvkit (haven't quite gotten used to all of xsv's syntax) and the t command-line tool: https://gist.github.com/dannguyen/7213b16b7de79ad9c89fc2297d0c822d https://gist.github.com/dannguyen/7213b16b7de79ad9c89fc2297d... 2. Prepping data for importing into SQLite; for very large public datasets, I like using xsv/csvkit to trim columns and filter out rows before importing them into SQLite. This is especially convenient since SQLite doesn't allow for dropping columns once a table is created. If I'm ever working with data big enough to need SQLite-like performance, then I probably need SQLite's query syntax and other features. xsv is already flexible and performant for what I need in my data pipeline.