473,837 Members | 1,657 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
from?

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

con=sqlite3.con nect(':memory:' )
con.isolation_l evel="EXCLUSIVE "
cur=con.cursor( )

def ins():
global con, data, cur

try:
cur.execute('dr op table test')
except:
pass
cur.execute('cr eate table test (key int primary key, val int not
null);')
con.commit()

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

hs = {}

print 'generating data...'
size = 1000000
data = [[a] for a in random.sample(x range(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])
f.close()

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

thanks, ben
Sep 5 '08 #1
0 1766

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

Similar topics

2
3632
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 SQLite libraries and headers installed before compiling Python, and the build process will compile the module when the necessary headers are available." I do have SQLite3 installed on my system, but after doing a
2
1981
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 new ...": """ # Create table c.execute('''create table stocks (date timestamp, trans varchar, symbol varchar, qty decimal, price decimal)''')
66
7100
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 has been upgraded, I can go ahead and install Python 2.5. So I open the manual to Section 13.13 where I find the first example of how to use Sqlite3:
0
1002
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 empty list. Anyone know the reason ??
4
8927
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. However, when I start python and do an import sqlite3 I get: /ptmp/bin/python Python 2.5.1 (r251:54863, May 29 2007, 05:19:30) on sunos5
13
1840
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
33
2578
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 switch to turn it on/off ? thanks, Stef Mientki
0
4139
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 calls. The relevant source code is here if you're interested: http://svn.python.org/projects/python/trunk/Modules/_sqlite/cursor.c
3
2980
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 information. Traceback (most recent call last): File "<stdin>", line 1, in <module> ImportError: No module named sqlite3 Yet:
0
9828
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9679
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10560
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10611
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7801
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6988
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5663
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5840
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4469
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.