Sqlite对CSV文件处理性能 [英] Sqlite vs CSV file manipulation performance

问题描述

推荐答案

除非你对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...

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


• 使用弹性查询语言进行查询。 >
  
• 利用高性能索引。


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

您可以查看这样的费用：

You can look at the costs like this:

• 一次...
  
  
  
  
  • 模式。
  
  
  • 将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屋！