Sqlite与CSV文件的处理性能 [英] Manipulation performance of Sqlite vs CSV file

问题描述

从操纵性能的角度来看，Sqlite还是CSV文件哪个更好？

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

推荐答案

除非您这样做非常对CSV来说很琐碎，并且只执行一次， SQLite在运行时，编码时间和维护时间上会更快，并且更加灵活 。

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.

将CSV放入SQLite的主要优点是...

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

• 使用已知查询语言进行查询。


• 使用灵活查询语言进行查询。


• 利用高性能索引。


• 不必编写，维护，记录和测试一堆自定义查询代码。

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

您可以查看以下成本：

• 一旦...
  
  
  
  
  • 创建架构。
  
  
  • 将CSV导入SQLite（内置）。
    
    
    
    
    • 此可能要求您编写一些代码来转换值。
    
    
    • Once...
      • Create the schema.
      • Import the CSV into SQLite (built in).
        • This may require you to write some code to translate the values.
        
        
        • 在SQL中进行查询。
        
        
        
        
        • 对于每个不同的查询。 ..
          
          
          
          
          • 为查询编写特殊代码。
          
          
          • 记录如何使用此特殊代码。
          
          
          • 测试您的特殊查询代码。
          
          
          • 调试您的特殊查询代码。
          
          
          • 运行必须执行的特殊查询代码。 ..
            
            
            
            
            • 读取CSV文件。
            
            
            • 解析CSV文件。
            
            
            • （可选）为CSV文件编制索引。
              
              
              
              
              • 使用索引方案。
              
              
              • For each different query...
                • Write special code for your query.
                • Document how to use this special code.
                • Test your special query code.
                • Debug your special query code.
                • Run your special query code which has to...
                  • Read the CSV file.
                  • Parse the CSV file.
                  • (Optional) Index the CSV file.
                    • Come up with an indexing scheme.

                    注意如果您的查询很简单，则解析和运行可以同时发生。诸如查找字段5大于10的所有列之类的东西。

                    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".

                    即使您使用，也很容易忘记如果库需要进行CSV解析，则编写特殊代码以查询CSV文件会产生编码和维护费用。 每个查询 都必须进行编码，测试和调试。 每个特殊情况或选项 都必须进行编码，测试和调试。

                    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.

                    相反，SQLite要求您在SQL查询之外编写很少或不需要编写特殊代码。 SQL是一种常见的查询语言。您可以说这是一个SQLite数据库，人们很可能会知道该怎么做。或者，他们将学习通常适用的SQL。学习特殊的CSV查询程序是一次性的知识。

                    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.

                    如果人们想运行查询，您没想到他们只能自己编写SQL。您不需要打扰，也不需要弄乱一堆代码。

                    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.

                    最后，SQLite的查询时间会好得多索引表比您或我可能写的任何东西都要多。 SQLite是一个由许多数据库专家合作的数据库。您可能不会胜过他们用C语言编写的经过仔细优化的代码。即使您可以提高性能，也可以您还有更好的事情要做吗？

                    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, don't you have better things to do?

                    这篇关于Sqlite与CSV文件的处理性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！