Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.os.linux.advocacy > #471761
| From | DFS <nospam@nospam.com> |
|---|---|
| Subject | Re: slrn is trembling |
| Newsgroups | comp.os.linux.advocacy |
| References | (6 earlier) <pk9a99ba3.l4a@rooftop.invalid> <a3ce18b3-1ab8-4a63-bfd9-c8c01a3ed760@googlegroups.com> <8z98bbkea98.l97@rooftop.invalid> <C4ovD.59004$UI2.10174@fx18.iad> <a0z8b0803.ah@rooftop.invalid> |
| Message-ID | <MfbwD.95808$fE2.90028@fx30.iad> (permalink) |
| Organization | blocknews - www.blocknews.net |
| Date | 2018-10-12 20:46 -0400 |
On 10/10/2018 3:21 PM, owl wrote:
> DFS <nospam@nospam.com> wrote:
>> On 10/9/2018 11:55 AM, owl wrote:
>>
>>> Holy shit! I wrapped the inserts in a transaction and now it does 2000
>>> inserts in half a second.
>>
>> That's blazing.
>>
>> MySQL?
>
> Yes.
>
>> You timed it from BEGIN TRANSACTION through commit()?
>>
>
> That was run time for the whole program.
>
>
>> python and SQLite on my older computer:
>> 0.12s to do 2K inserts of a 1626-byte header string
>> 2.11s to commit the data
That was committing to a table in my 14GB Usenet db. Using a new
sqlite3 db file the results were as fast as:
0.10s to insert data
0.26s to commit data
>> import sqlite3,time,DFS_dbset
>> conn,db,dbName,ps = DFS_dbset.openconnection('sqlite')
>> db.execute("DROP TABLE IF EXISTS TESTHDR;")
>> db.execute("CREATE TABLE TESTHDR (HDRID INTEGER PRIMARY KEY
>> AUTOINCREMENT, HDRVAL TEXT);")
>> header = <snipped for brevity>
>> start = time.clock()
>> db.execute("BEGIN TRANSACTION;")
>> for i in range(0,2000):
>> db.execute("INSERT INTO TESTHDR (HDRVAL) VALUES(?);",(header,))
>> print "%.2fs to insert data" % (time.clock() - start)
>> start = time.clock()
>> conn.commit()
>> print "%.2fs to commit data" % (time.clock() - start)
>> db.close()
>> conn.close()
>
> Had to modify your code a bit since I don't have "DFS_dbset".
It's just a generic connection manager routine used to shorten the
database login/connection in the calling program.
===================================================================
DFS_dbset.py
===================================================================
import sqlite3, psycopg2, mysql.connector
def openconnection(dbtype):
if dbtype == "sqlite":
dbDir = "D:\\development\\SQLite\\"
dbName = "USENET.sqlite"
conn = sqlite3.connect(dbDir + dbName)
ps = '?' #query parameter symbol
if dbtype == "postgres":
dbName = "USENET on PostgreSQL"
connectstring = "" \
" host = 'localhost' " \
" dbname = 'USENET' " \
" user = 'user' " \
" password = 'pw' "
conn = psycopg2.connect(connectstring)
ps = '%s' #query parameter symbol
if dbtype == "mysql":
dbName = "USENET on MySQL"
connectstring = {
'host' : '127.0.0.1',
'database': 'USENET',
'user' : 'user',
'password': 'pw',
}
conn = mysql.connector.connect(connectstring)
ps = '%s' #query parameter symbol
db = conn.cursor()
return [conn,db,dbName,ps]
===================================================================
> Using 2000 inserts of 1626-byte header:
>
> anon@lowtide:~/usenet/database$ ./times
>
> python sqlite3:
>
> 0.07s to insert data
> 0.01s to commit data
>
> real 0m1.079s
> user 0m0.080s
> sys 0m0.084s
>
> C sqlite3:
>
> 0.08s to insert data
> 0.01s to commit data
>
> real 0m1.065s
> user 0m0.100s
> sys 0m0.048s
> C mysql:
>
> 0.03s to insert data
> 0.00s to commit data
>
> real 0m0.441s
> user 0m0.012s
> sys 0m0.016s
>
> anon@lowtide:~/usenet/database$
>
Back to comp.os.linux.advocacy | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-04 10:08 -0400
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-04 08:51 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-04 13:50 -0400
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-04 11:59 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-04 21:23 -0400
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 08:12 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 08:21 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 09:55 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-05 13:51 -0400
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 11:16 -0700
"Path:" is the only line that's never fake. Jeff-Relf.Me @. - 2018-10-14 01:41 -0700
Re: "Path:" is the only line that's never fake. "Sir Gregory Hall, Esq." <greghall@yacht_maester.fake> - 2018-10-14 11:16 -0400
Re: "Path:" is the only line that's never fake. Steve Carroll <frelwizzen@gmail.com> - 2018-10-14 08:38 -0700
Re: "Path:" is the only line that's never fake. snipeco.2@gmail.com (Sn!pe) - 2018-10-14 16:40 +0100
Re: "Path:" is the only line that's never fake. "Sir Gregory Hall, Esq." <greghall@yacht_maester.fake> - 2018-10-14 11:43 -0400
Re: "Path:" is the only line that's never fake. snipeco.2@gmail.com (Sn!pe) - 2018-10-14 17:12 +0100
Re: "Path:" is the only line that's never fake. "Sir Gregory Hall, Esq." <greghall@yacht_maester.fake> - 2018-10-14 12:23 -0400
Re: "Path:" is the only line that's never fake. snipeco.2@gmail.com (Wader of Doom) - 2018-10-14 18:01 +0100
No one can do "a perfect forgery" of my posts. Jeff-Relf.Me @. - 2018-10-14 14:29 -0700
Re: No one can do "a perfect forgery" of my posts. "Sir Gregory Hall, Esq." <greghall@yacht_maester.fake> - 2018-10-14 17:33 -0400
Re: No one can do "a perfect forgery" of my posts. snipeco.2@gmail.com (Sn!pe) - 2018-10-14 22:47 +0100
Re: No one can do "a perfect forgery" of my posts. "Sir Gregory Hall, Esq." <greghall@yacht_maester.fake> - 2018-10-14 17:53 -0400
Re: No one can do "a perfect forgery" of my posts. snipeco.2@gmail.com (Sn!pe) - 2018-10-14 22:58 +0100
Re: No one can do "a perfect forgery" of my posts. Steve Carroll <frelwizzen@gmail.com> - 2018-10-14 14:56 -0700
Re: No one can do "a perfect forgery" of my posts. Steve Carroll <frelwizzen@gmail.com> - 2018-10-14 14:41 -0700
Re: "Path:" is the only line that's never fake. Steve Carroll <frelwizzen@gmail.com> - 2018-10-14 08:54 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-14 11:26 -0400
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-14 09:23 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-14 18:53 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-14 13:27 -0700
Re: slrn is trembling Melzzzzz <Melzzzzz@zzzzz.com> - 2018-10-14 20:55 +0000
Pipelining "Article" commands. Jeff-Relf.Me @. - 2018-10-14 14:23 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-15 13:49 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-15 07:34 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-15 21:54 -0400
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-15 19:15 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-15 21:51 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-16 03:16 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-15 20:33 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-16 10:26 -0400
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-16 07:44 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-16 15:17 +0000
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-16 12:26 -0400
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-16 09:46 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-16 10:21 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-16 14:00 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-16 16:49 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 12:35 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-04 20:44 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 14:09 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-04 14:50 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-04 22:32 +0000
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-04 23:03 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 16:28 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-04 16:54 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 00:02 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-04 17:20 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 17:31 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 00:32 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-04 17:41 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 00:54 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 18:02 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 01:07 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-04 18:11 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 18:48 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 02:06 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 08:01 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 15:35 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 08:37 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 08:53 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 08:57 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 09:17 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 09:19 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 09:41 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 16:19 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 09:23 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 16:40 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 09:41 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 17:39 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 11:07 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 18:14 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 11:41 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 18:52 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-06 08:46 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-06 11:29 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-06 11:48 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-06 11:51 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-06 12:19 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-06 15:34 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-08 21:27 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-08 17:49 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-08 18:13 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-09 07:31 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-09 13:45 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-09 07:55 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-09 15:55 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-09 09:20 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-09 09:39 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-09 09:41 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-09 10:23 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-09 23:38 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-09 17:47 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-09 11:09 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-10 09:04 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-10 13:56 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-11 05:02 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-10 10:33 -0400
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-10 19:21 +0000
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-12 20:46 -0400
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-12 23:50 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-08 18:44 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-06 14:08 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 11:32 -0700
Re: slrn is trembling "F. Russell" <fr@random.info> - 2018-10-05 00:32 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 17:46 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 00:55 +0000
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 01:17 +0000
Re: slrn is trembling chrisv <chrisv@nospam.invalid> - 2018-10-05 06:59 -0500
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 05:14 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 12:48 +0000
Re: slrn is trembling chrisv <chrisv@nospam.invalid> - 2018-10-05 11:17 -0500
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-05 13:35 -0400
Re: slrn is trembling Silicon Vee <siliconvee1945@yahoo.com> - 2018-10-11 01:36 +0200
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-04 17:07 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 00:12 +0000
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-04 17:21 -0700
Re: slrn is trembling SilverSlimer <silver@slim.er> - 2018-10-05 12:01 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 05:30 -0700
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 08:13 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-05 12:50 -0400
Re: slrn is trembling Steve Carroll <fretwizzer@gmail.com> - 2018-10-05 10:35 -0700
Re: slrn is trembling chrisv <chrisv@nospam.invalid> - 2018-10-10 08:51 -0500
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-10 07:09 -0700
Re: slrn is trembling Sam O'Nella <poison@donteatit.net> - 2018-10-05 16:43 -0400
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-10 10:34 -0400
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-10 07:56 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-10 12:38 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-11 02:12 -0700
Re: slrn is trembling Snit <usenet@gallopinginsanity.com> - 2018-10-12 12:27 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-12 12:42 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-12 21:47 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-14 03:28 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-12 20:45 -0700
Re: slrn is trembling Snit <usenet@gallopinginsanity.com> - 2018-10-05 15:15 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 15:41 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 19:21 -0700
Re: slrn is trembling Snit <usenet@gallopinginsanity.com> - 2018-10-05 13:44 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-05 17:48 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 17:15 -0700
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 16:13 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-04 20:47 -0400
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 01:09 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 18:18 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-04 21:38 -0400
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 19:04 -0700
Re: slrn is trembling owl <owl@rooftop.invalid> - 2018-10-05 02:05 +0000
Re: slrn is trembling Steve Carroll <frelwizzen@gmail.com> - 2018-10-04 19:35 -0700
Re: slrn is trembling DFS <nospam@nospam.com> - 2018-10-10 10:36 -0400
csiph-web