blocks|key|4565130|text|一个明显的优点是您不能索引csv文件。如果必须使用大型数据集的子集，则在sqlite表中的列上创建索引是一种优势。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4565131|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

One clear advantage is that you cannot index a csv file. If you have to use subsets of your large data set, creating an index on the column in the sqlite table is an advantage.

blocks|key|407489|text|除非你对CSV做了一些非常琐碎的事情，而且只做了一次，否则SQLite在运行时、编码时间和维护时间上都会更快，而且它的也会更灵活。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|407490|将CSV放入SQLite的主要优点是...|407491|使用已知的查询language.|407492|Query使用灵活的查询语言进行|unordered-list-item|407493|查询。利用高性能indexing.|407494|Don't的优势，|407495|必须编写、维护、记录和测试一系列自定义查询代码。|407496|407497|407498|407499|您可以像这样查看成本：|407500|SQLite|407501|407502|曾经..。将CSV创建为schema.|407503|Import+|407504|+SQLite+(内置)。|407505|这可能需要您编写一些代码来转换values.|407506|407507|407508|407509|​|407510|-+[Optional,+but+recommended]+Set+up+the+indexes.|code-block|syntax|javascript|407511|407512|每个不同查询的|407513|...在SQL.|407514|中执行|407515|查询|407516|407517|407518|407519|CSV|407520|407521|...|407522|为您的query.|407523|Document编写特殊代码如何使用此特殊代码。|407524|测试您的特殊查询代码。|407525|调试您的特殊查询代码。|407526|运行您的特殊查询代码，它必须...|407527|读取CSV文件。|407528|解析CSV文件索引CSV文件。|407529|提出了一个索引scheme.|407530|407531|407532|407533|407534|407535|++++-+Run+your+query.|407536|407537|407538|请注意，如果您的查询很简单，那么解析和运行可以同时进行。类似于“查找字段5大于10的所有列”。|407539|407540|很容易忘记，即使您使用库来执行CSV解析，编写特殊代码来查询CSV文件也会产生编码和维护成本。每个查询都必须经过编码、测试和调试。每个特殊情况或选项都必须经过编码、测试和调试。|407541|因为它都是你编造的特殊东西，所以没有惯例可遵循。要使用你的查询程序的人必须了解它的功能和工作原理。如果他们想要做任何稍微不同的事情，他们(或您)必须进入代码，理解它，修改它，测试它，调试它，并记录它。这将产生大量的支持请求。|407542|相反，除了SQLite查询之外，只需要编写很少的代码，甚至不需要编写任何特殊的代码。SQL是一种众所周知的查询语言。你可以说“这是一个SQLite数据库”，人们很可能会知道该怎么做。或者，他们会去学习SQL，这是一般适用的知识。而学习特殊的CSV查询程序是一次性的知识。|407543|如果人们想要运行您没有预料到的查询，他们可以自己编写SQL。你不需要被打扰，他们也不需要费力写出一堆代码。|407544|最后，使用索引良好的表，SQLite的查询时间将比您或我可能编写的任何内容都要快得多。SQLite是一个由许多数据库专家协作开发的数据库。您可能无法超越他们用C编写的经过精心优化的代码，即使您可以在性能上略胜一筹，don't+you+have+better+things+to+do|407545|entityMap|0|LINK|mutability|MUTABLE|url|https://en.wikipedia.org/wiki/Opportunity_cost^0|T|U|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|2|2|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1|1|1|1|2|2|3|3|2|1|0|0|0|0|0|0|0|0|1B|4|1T|9|0|0|0|0|2Z|Y|0|0^^$0|@$1|2|3|4|5|6|7|39|8|@$9|3A|A|3B|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|3C|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|3D|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|3E|8|@]|D|@]|E|$]]|$1|M|3|N|5|L|7|3F|8|@]|D|@]|E|$]]|$1|O|3|P|5|L|7|3G|8|@]|D|@]|E|$]]|$1|Q|3|R|5|L|7|3H|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|3I|8|@]|D|@]|E|$]]|$1|T|3|-4|5|6|7|3J|8|@]|D|@]|E|$]]|$1|U|3|-4|5|6|7|3K|8|@]|D|@]|E|$]]|$1|V|3|W|5|6|7|3L|8|@]|D|@]|E|$]]|$1|X|3|Y|5|6|7|3M|8|@]|D|@]|E|$]]|$1|Z|3|-4|5|6|7|3N|8|@]|D|@]|E|$]]|$1|10|3|11|5|L|7|3O|8|@]|D|@]|E|$]]|$1|12|3|13|5|L|7|3P|8|@]|D|@]|E|$]]|$1|14|3|15|5|L|7|3Q|8|@]|D|@]|E|$]]|$1|16|3|17|5|L|7|3R|8|@]|D|@]|E|$]]|$1|18|3|-4|5|6|7|3S|8|@]|D|@]|E|$]]|$1|19|3|-4|5|6|7|3T|8|@]|D|@]|E|$]]|$1|1A|3|-4|5|6|7|3U|8|@]|D|@]|E|$]]|$1|1B|3|1C|5|6|7|3V|8|@]|D|@]|E|$]]|$1|1D|3|1E|5|1F|7|3W|8|@]|D|@]|E|$1G|1H]]|$1|1I|3|1C|5|6|7|3X|8|@]|D|@]|E|$]]|$1|1J|3|1K|5|6|7|3Y|8|@]|D|@]|E|$]]|$1|1L|3|1M|5|L|7|3Z|8|@]|D|@]|E|$]]|$1|1N|3|1O|5|6|7|40|8|@]|D|@]|E|$]]|$1|1P|3|1Q|5|L|7|41|8|@]|D|@]|E|$]]|$1|1R|3|-4|5|6|7|42|8|@]|D|@]|E|$]]|$1|1S|3|1C|5|6|7|43|8|@]|D|@]|E|$]]|$1|1T|3|-4|5|6|7|44|8|@]|D|@]|E|$]]|$1|1U|3|1V|5|6|7|45|8|@]|D|@]|E|$]]|$1|1W|3|1K|5|6|7|46|8|@]|D|@]|E|$]]|$1|1X|3|1Y|5|L|7|47|8|@]|D|@]|E|$]]|$1|1Z|3|20|5|L|7|48|8|@]|D|@]|E|$]]|$1|21|3|22|5|L|7|49|8|@]|D|@]|E|$]]|$1|23|3|24|5|L|7|4A|8|@]|D|@]|E|$]]|$1|25|3|26|5|L|7|4B|8|@]|D|@]|E|$]]|$1|27|3|28|5|L|7|4C|8|@]|D|@]|E|$]]|$1|29|3|2A|5|L|7|4D|8|@]|D|@]|E|$]]|$1|2B|3|2C|5|L|7|4E|8|@]|D|@]|E|$]]|$1|2D|3|2E|5|L|7|4F|8|@]|D|@]|E|$]]|$1|2F|3|-4|5|6|7|4G|8|@]|D|@]|E|$]]|$1|2G|3|-4|5|6|7|4H|8|@]|D|@]|E|$]]|$1|2H|3|-4|5|6|7|4I|8|@]|D|@]|E|$]]|$1|2I|3|-4|5|6|7|4J|8|@]|D|@]|E|$]]|$1|2J|3|1C|5|6|7|4K|8|@]|D|@]|E|$]]|$1|2K|3|2L|5|1F|7|4L|8|@]|D|@]|E|$1G|1H]]|$1|2M|3|1C|5|6|7|4M|8|@]|D|@]|E|$]]|$1|2N|3|1C|5|6|7|4N|8|@]|D|@]|E|$]]|$1|2O|3|2P|5|6|7|4O|8|@]|D|@]|E|$]]|$1|2Q|3|-4|5|6|7|4P|8|@]|D|@]|E|$]]|$1|2R|3|2S|5|6|7|4Q|8|@$9|4R|A|4S|B|C]|$9|4T|A|4U|B|C]]|D|@]|E|$]]|$1|2T|3|2U|5|6|7|4V|8|@]|D|@]|E|$]]|$1|2V|3|2W|5|6|7|4W|8|@]|D|@]|E|$]]|$1|2X|3|2Y|5|6|7|4X|8|@]|D|@]|E|$]]|$1|2Z|3|30|5|6|7|4Y|8|@]|D|@$9|4Z|A|50|1|51]]|E|$]]|$1|31|3|-4|5|6|7|52|8|@]|D|@]|E|$]]]|32|$33|$5|34|35|36|E|$37|38]]]]

Unless you're doing something very trivial to the CSV, and only doing it once, SQLite will be faster for runtime, coding time, and maintenance time, and it will be more flexible.

The major advantages of putting the CSV into SQLite are...

<ul>
<li>Query with a known query language.</li>
<li>Query with a flexible query language.</li>
<li>Take advantage of high performance indexing.</li>
<li>Don't have to write and maintain and document and test a bunch of custom query code.</li>
</ul>

<hr>

You can look at the costs like this:

<h1>SQLite</h1>

<ul>
<li>Once...

<ul>
<li>Create the schema.</li>
<li>Import the CSV into SQLite (built in).

<ul>
<li>This may require you to write some code to translate the values.</li>
</ul></li>
<li>[Optional, but recommended] Set up the indexes.</li>
</ul></li>
<li>For each different query...

<ul>
<li>Do your query in SQL.</li>
</ul></li>
</ul>

<hr>

<h1>CSV</h1>

<ul>
<li>For each different query...

<ul>
<li>Write special code for your query.</li>
<li>Document how to use this special code.</li>
<li>Test your special query code.</li>
<li>Debug your special query code.</li>
<li>Run your special query code which has to...

<ul>
<li>Read the CSV file.</li>
<li>Parse the CSV file.</li>
<li>(Optional) Index the CSV file.

<ul>
<li>Come up with an indexing scheme.</li>
</ul></li>
<li>Run your query.</li>
</ul></li>
</ul></li>
</ul>

Note that if your query is simple parsing and running can happen together. Something like "find all columns where field 5 is greater than 10".

<hr>

It's easy to forget that even if you use a library to do the CSV parsing, there are coding and maintenance costs to writing special code to query a CSV file. Every query has to be coded, tested, and debugged. Every special case or option has to be coded, tested, and debugged.

Since it's all special stuff you made up, there's no convention to follow. People coming to use your query program have to understand what it does and how it works. If they want to do anything even slightly different, they (or you) have to get into the code, understand it, modify it, test it, debug it, and document it. This will generate a lot of support requests.

In contrast, SQLite requires you to write little or no special code beyond the SQL queries. SQL is a commonly known query language. You can say "this is a SQLite database" and it's very likely people will know what to do. Alternatively they'll go learn SQL which is generally applicable knowledge. Whereas learning your special CSV query program is one-off knowledge.

If people want to run a query you didn't anticipate they can just write the SQL themselves. You don't need to be bothered, and they don't need to puzzle out a bunch of code.

Finally, SQLite's query time will be far better with a well indexed table than anything you or I are likely to write. SQLite is a database collaborated on by many, many database experts. You're probably not going to outperform the carefully optimized code they've written in C. Even if you can edge out a bit of performance, <a href="https://en.wikipedia.org/wiki/Opportunity_cost" rel="noreferrer">don't you have better things to do</a>?

As a matter of manipulation performance, which one is better, Sqlite or CSV file?

问Sqlite与CSV文件的操作性能
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Sqlite与CSV文件的操作性能EN