为什么Windows7上的插入和删除要比Mac10.9长100多倍？

Question

我编写了一个脚本来对插入和删除进行基准测试。你知道吗

import os, time

abspath =  os.path.abspath(os.path.dirname(__file__))
dbname = 'test.sqlite'
# dbname = ':memory:'

databaseFileName = os.path.join(abspath, dbname)
if os.path.exists(databaseFileName):
    os.remove(databaseFileName)


from sqlalchemy import \
    Table, Column, MetaData, create_engine,\
    Integer, DateTime
engine = create_engine('sqlite:///' + dbname)

metadata = MetaData()
test = Table ('test', metadata,
    Column('id', Integer, primary_key=True)
)
metadata.create_all(engine)

conn = engine.connect()

numRecords = 100

start = time.clock()
for i in range(numRecords):
    conn.execute(test.insert())
print 'It took %s seconds to insert %s records' % ((time.clock() - start), numRecords)

start = time.clock()
for i in range(1, numRecords+1):
    conn.execute(test.delete().where(test.c.id == i))
print 'It took %s seconds to delete %s records' % ((time.clock() - start), numRecords)

在窗户上印的

It took 5.32831616059 seconds to insert 100 records
It took 6.76065831351 seconds to delete 100 records

在Mac上打印

It took 0.036788 seconds to insert 100 records
It took 0.041629 seconds to delete 100 records

为什么在Mac上速度这么快？是不是因为Mac使用HFS+而Windows使用NTFS？你知道吗

Answer 1

显然SQLite在Mac和Win上的表现一直是一个反复出现的问题。请参阅此讨论from 2012。你知道吗

一个关键特性似乎是同步参数。你知道吗

从讨论中：

On Windows Sqlite by default uses the real/full fsyncs to the hardware as provided by the OS, while on at least OS/X, by default it doesn't. See http://sqlite.org/pragma.html#pragma_fullfsync

你能试着用pragma sync=off运行吗？你知道吗