踩坑(Running)填坑(ZSSURE):SQLite数据库操作效率分析

背景:

之前对于数据库操作部分大多停留在会使用阶段,并未真正考虑过数据库SQL语句的执行效率问题。近期响应项目组其他成员需要,协助手动修改数据库。为了提高工作效率编写了C#测试工程进行相关操作。由于数据库记录量较大(超过10K条),在编写程序过程中发现不同的操作执行效率相差甚远,特做记录如下,待后续有时间再深入分析各种数据库的SQL语句的执行效率。

此次数据库修改要求涉及到删除、修改、添加等各种操作,为了简单对比各种方式下程序的执行效率,只选择“删除”操作进行分析。详情如下:

for循环逐条删除:

起初最直接的思路是通过for循环逐个删除符合条件的记录,代码如下所示:

执行效果如下:

【知识点补充】:在使用循环操作过程中,可以看到在数据库文件目录下生成了XXX.db3-journal的临时文件,且大小随时在变动。(关于journal文件的描述参见博文journal是SQLite用于回滚操作的暂存文档,当数据库写入失败时,通过journal文档可以复原到未更改之前的数据,通过观察本机目录下的journal文件发现,其大小在随时变动因此可以猜测,代码中循环的每一步都在进行数据库打开、写入操作,因此导致耗时超出容忍极限。

使用事务(Transaction):

SQLite缺省为每个操作启动一个事务,也就是上述循环代码中每一次DELETE以及每一次SELECT都启动了一个事务,如是“事务开启+SQL执行+事务关闭”自然耗费了大量的时间。而手动显式调用SQLiteTransaction将循环中的所有SQL操作放到同一个事务中,此时只需要一次事务开启和关闭的时间即可。(参考博文

另外SQLite的数据库本质上就是一个磁盘上的文件,所以一切的数据库操作都会转化为对文件的操作,而频繁的文件操作会是一个耗时的I/O过程,极大的影响数据库的存取速度。

显式调用Transaction的代码如下:

执行效果为:

由此可见,效率较之前“暴力”方式有了明显数量级的提升,而且在程序执行过程中看不到XXX.db3-journal临时文件的生成。

使用LIKE查询+组合SQL:

再分析一下上述操作流程可以发现,之前的操作是分两部分完成的:第一步,SELECT查询满足要求的待删除数据;第二,使用for循环逐条DELETE之前SELECT的结果。这两次操作之间既开辟了多余的List变量,又重复操作了SQLite数据库文件,因此执行效率还有待提高。

这里用LIKE进行条件查询,然后使用组合SQL语句直接删除满足条件的结果,具体代码如下:

执行效果如下:

由此可见在使用事务Transaction的基础上,通过改进程序整体流程又一次减少了数据库打开、关闭的次数,效率有了进一步的提升。对比上述三种操作效率,结果如下:

总结:

随着ORM技术的出现,对于数据库的操作越来越简单,之前也介绍过使用Telerik OpenAccess来进行数据库相关操作。正是由于诸多新技术的出现使得在项目实际过程中越来越不注重最底层的、最基本的执行效率,对于单条SQL语句、单次操作往往不做任何考虑,直接使用最原始的“野蛮”方式。另外由于之前项目大多是单机版,不会出现WEB应用中海量用户多并发,因此对于数据库的执行效率感受就不够深刻。随着云计算、大数据、分布式的逐渐普及,数据库技术(尤其是执行效率)会越发重要,【切记】后续在此方面要多用心。

作者:[email protected]

时间:2015/08/16

版权声明:本文为博主原创文章,未经博主允许不得转载。

时间: 08-15

踩坑(Running)填坑(ZSSURE):SQLite数据库操作效率分析的相关文章

【踩坑(Running)填坑(ZSSURE)】:WCF学习之InstanceContextMode与ConcurrencyMode

背景: WCF可以看作是微软对SOA架构的一种实现,或者说WCF的存在让开发者更容易创建面向服务的程序.面向服务本身不是一种技术,而是设计和实现软件的一种架构方式.从最早的面向过程(PO).面向对象编程(OO),到后来的面向服务(SO).面向资源(RO)编程,本身没有本质区别,反映出的是人们认识世界的方法论的迭代进化. PO.OO.SO.RO,还有一个不沾边的O2O,各种概念层出不穷,在学习使用时要切忌混淆.今天这里记录的是在WCF框架下(面向服务架构--SOA--的一种实现),如何进行多线程及

移动前端:坑与填坑

1.页面高度渲染错误 坑:页面底部部分与浏览器导航条重合了 填坑:重置高度 document.documentElement.style.height = window.innerHeight + 'px'; 2.transform碰上模糊 坑:在android中,如果元素或其父元素应用transform后,元素设置border-radius会变模糊 填坑:先放大再缩小 body{padding: 20px;background:purple;-webkit-transform: transla

纯Socket(BIO)长链接编程的常见的坑和填坑套路

本文章纯属个人经验总结,伪代码也是写文章的时候顺便白板编码的,可能有逻辑问题,请帮忙指正,谢谢. Internet(全球互联网)是无数台机器基于TCP/IP协议族相互通信产生的.TCP/IP协议族分了四层实现,链路层.网络层.传输层.应用层. 与我们应用开发者接触最多的应该是应用层了,例如web应用普遍使用HTTP协议,HTTP协议帮助我们开发者做了非常多的事情,通过HTTP足以完成大部分的通信工作了,但是有时候会有一些特殊的场景出现,使得HTTP协议并不能得心应手的完成工作,这个时候就需要我们

NHiberante从.net framework转移到.net standard(.net core 2.2)时遇到的坑及填坑

在.net framework中的创建session代码先贴一个 1 public class SessionBuilder 2 { 3 private static ISessionFactory _sessionFactory = null; 4 5 public SessionBuilder() 6 { 7 if (_sessionFactory == null) 8 { 9 //创建ISessionFactory 10 _sessionFactory = GetSessionFactor

开坑,填坑——莫比乌斯反演

hdu 1695 题目:给出x和y的范围,要求gcd(x,y)==k的数对个数. 思路:首先把范围除k,然后就是求gcd(x,y)=1的数对个数.具体莫比乌斯公式的用法还不是很懂,目前的理解是这样的: 莫比乌斯公式给出了一个从和函数反演到原函数的方法.对于一个定义在正整数上的函数,其和函数F(n)定义为所有f(d)|d是n的因子的和.然后根据莫比乌斯公式,可由F求得f. 但是此题的形式有点不同.设f(k)表示gcd(x,y)=k的数对的个数.然后设F(k)表示gcd(x,y)=k的倍数的数对的个

【填坑纪事】一次用System.nanoTime()填坑System.currentTimeMills()的实例记录

JDK提供了两个方法,System.currentTimeMillis()和System.nanoTime(),这两个方法都可以用来获取表征当前时间的数值.但是如果不仔细辨别这两个方法的差别和联系,在使用当中也很容易出错.笔者在前不久的工作当中使用System.currentTimeMillis()时就踩了一个大坑,后来在查明System.currentTimeMillis()和System.nanoTime()的特性后,才用System.nanoTime()来填了这个坑.本文,笔者就以自己的踩

挨踢部落故事汇(32): Java深坑如何填?

世上本没有坑,踩的人多了也便成了坑.每遇到一次困难,每踩一个坑,对程序员来说都是一笔财富.持续学习是程序员保持竞争力的源泉.本期将分享一个踩坑无数的Java程序猿填坑秘籍. 榆木,一个阅历无数(踩坑)的技术宅男,喜欢了解新技术却不爱太钻研新技术(因为懒,猿届反面角色一枚).14年毕业至今,在Java开发这条道路上可谓是坑过好些人.也埋过好些坑.也被坑过好些次.因为懒,没有针对他遇到过的问题做过太多的笔记(记录一些棘手问题的解决方法还是个不错的习惯),只是习惯性的去分析为什么出现这样的问题,我们该

支付宝和微信支付的各种填坑

填坑 支付宝填坑是每个接入支付宝必经之路,下面是我接入支付宝遇到的问题汇总,希望大家在接入的路上少一点弯路 问题1. Util/base64.h:63:21: Cannot find interface declaration for ‘NSObject’, superclass of ‘Base64’ 解决办法: 这是base64.h中没有加入#import 系统库文件导致,这个错误报错方法直接想喷它一脸.报错方式太恶心. 1 2 解决办法: 这是base64.h中没有加入#import  系

10分钟搞定支付宝和微信支付 的 各种填坑

填坑   支付宝填坑是每个接入支付宝必经之路,下面是我接入支付宝遇到的问题汇总,希望大家在接入的路上少一点弯路 问题1. Util/base64.h:63:21: Cannot find interface declaration for ‘NSObject’, superclass of ‘Base64’ 解决办法: 这是base64.h中没有加入#import  系统库文件导致,这个错误报错方法直接想喷它一脸.报错方式太恶心. 问题2.截图告知你什么问题 解决办法: 这个问题可以同上的,心情