472,358 Members | 1,819 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,358 software developers and data experts.

sqlite3 import performance

hi folks --

a quick python and sqlite3 performance question. i find that
inserting a million rows of in-memory data into an in-memory database
via a single executemany() is about 30% slower than using the sqlite3
CLI and the .import command (reading the same data from a disk file,
even.) i find this surprising, executemany() i assume is using a
prepared statement and this is exactly what the .import command does
(based on my quick perusal of the source.)

is this discrepancy to be expected? where is the overhead coming

for full disclosure: the python code is at the end; run it first to
generate the data file. to test the CLI, i couldn't find a better way
than to create an init file "sqlcmds" containing

create table test (k int primary key, v int not null);
..import data test

and then run

time sqlite3 -init sqlcmds ':memory:' '.quit'

the python code is

#!/usr/bin/env python

import sqlite3, random, timeit

con = None
def prepare():
global con, cur


def ins():
global con, data, cur

cur.execute('drop table test')
cur.execute('create table test (key int primary key, val int not

cur.executemany("INSERT into test (key, val) values (?, 10)",data)

hs = {}

print 'generating data...'
size = 1000000
data = [[a] for a in random.sample(xrange(10000000), size)]
print 'done!'

# save the data for the sqlite3 CLI
f = file('data', 'w')
f.writelines([str(a[0])+"|10\n" for a in data])

print 'testing ' + str(size) + ' inserts...'
# test 100K inserts
t = timeit.Timer(stmt='ins()',
setup="import sqlite3\nfrom __main__ import prepare, ins
print t.repeat(3,1)

thanks, ben
Sep 5 '08 #1
0 1632

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Harold Shore | last post by:
From the release notes I read that "If you're compiling the Python source yourself, note that the source tree doesn't include the SQLite code, only the wrapper module. You'll need to have the...
by: John Machin | last post by:
Apologies in advance if this is a bit bloggy, but I'd like to get comments on whether I've lost the plot (or, more likely, failed to acquire it) before I start reporting bugs etc. From "What's...
by: mensanator | last post by:
Probably just me. I've only been using Access and SQL Server for 12 years, so I'm sure my opinions don't count for anything. I was, nevertheless, looking forward to Sqlite3. And now that gmpy...
by: jim-on-linux | last post by:
Python help, I just started working with SQLite3 and ran into this problem. Below, the first select produces results but, after closing then re-opening the database the select produces an...
by: Simon | last post by:
I installed the source code on unix for python 2.5.1. The install went mainly okay, except for some failures regarding: _ssl, _hashlib, _curses, _curses_panel. No errors regarding sqlite3....
by: mark carter | last post by:
I hesitate to ask, but ... I'm using Ubuntu Feisty: * Python 2.5.1 (r251:54863, May 2 2007, 16:56:35) on linux2 * SQLite version 3.3.13 Suppose I run the following program: import sqlite3
by: Stef Mientki | last post by:
hello, I discovered that boolean evaluation in Python is done "fast" (as soon as the condition is ok, the rest of the expression is ignored). Is this standard behavior or is there a compiler...
by: David | last post by:
- Are there any peculiarities with using curs.executemany(...) vs. multiple How many times are you calling execute vs a single executemany? The python call overhead will add up for thousands of...
by: milan_sanremo | last post by:
I have sqlite installed, but when I try to import sqlite3 I receive: Python 2.5.1 (r251:54863, Nov 3 2007, 02:54:36) on sunos5 Type "help", "copyright", "credits" or "license" for more...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it so the python app could use a http request to get...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.