RE: [sqlite] How fast is the sqlite connection created?
While cursors are generic to all databases, to me the test and code sample
seems to be very specific to Python. Looking at the code I can't imagine
there are actually any SQLite C calls within the cursor() method.
sqlite3.Connection() undoubtedly maps to sqlite3_open() and cursor.execute()
would map to sqlite3_exec (or more likely the lower-level functions). But
there's nothing in SQLite that I can see which would map to cursor() (i.e.,
nothing between opening the connection and preparing a statement).
For example in .NET an analogous routine would be:
using(DbConnection cnn = factory.CreateConnection()) {
cnn.ConnectionString = "...";
cnn.Open();
using(DBCommand cmd = cnn.CreateCommand()) {
cmd.CommandTest = "SELECT * FROM TABLE";
using(DbDataReader reader = cmd.ExecuteReader()) {
... read rows here ...
}
}
}
So the cursor() method in Python is somewhat like the CreateCommand() method
in .NET and in .NET CreateCommand is just an internal object allocation, it
has nothing to do with SQLite. However, in .NET we would also want to reuse
commands just like we use connections 'cause a command represents a parsed
statement.
Best regards,
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 01, 2007 10:50 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How fast is the sqlite connection created?
A cursor is the thing that you use to run your queries. Eg in Python's
wrappers you import the wrapper (library, module) Connections to the
database and create cursors on those Connections to do the actual work.
import sqlite3
conn=sqlite3.Connection(dbname)
crsr=conn.cursor()
crsr.execute("select * trom table")
result_set=crsr.fetchone()
...
result_set=crsr.fetchall()
and so on. SQLite cursors can only move forward in the result set. AIUI
cursors in some older/bigger databases can move in either direction.
FWIW it looks like calling cursor() takes ~1.9us on my machine with
Python2.5, sqlite3, disk file with schema of "create table t(a,b,c)".
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How fast is the sqlite connection created?
Samuel R. Neff wrote:
Eric,
Sorry if this is obvious to everyone else but not to me.. what exactly is
cursor()? I don't see it anywhere in the C API and the wrapper I'm using
(SQLite .NET) doesn't have any corresponding method.
A cursor is the thing that you use to run your queries. Eg in Python's
wrappers you import the wrapper (library, module) Connections to the
database and create cursors on those Connections to do the actual work.
import sqlite3
conn=sqlite3.Connection(dbname)
crsr=conn.cursor()
crsr.execute("select * trom table")
result_set=crsr.fetchone()
...
result_set=crsr.fetchall()
and so on. SQLite cursors can only move forward in the result set. AIUI
cursors in some older/bigger databases can move in either direction.
FWIW it looks like calling cursor() takes ~1.9us on my machine with
Python2.5, sqlite3, disk file with schema of "create table t(a,b,c)".
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] How fast is the sqlite connection created?
Eric, Sorry if this is obvious to everyone else but not to me.. what exactly is cursor()? I don't see it anywhere in the C API and the wrapper I'm using (SQLite .NET) doesn't have any corresponding method. In any case, only true way to know how expensive it is is to do some testing. The closer the test is to your real schema/data the more applicable will be the test to your situation. For example, my testing found that open takes 17 ms for my schema, but simpler schemas require only one or two. All testing is relative to exactly what is being tested. Best regards, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Eric S. Johansson [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:30 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How fast is the sqlite connection created? Samuel R. Neff wrote: > Some of this performance gain is probably related to caching data and query > plan, not just opening the connection, but still that caching is connection > related and is lost when you close the connection so it's a very real-world > valid comparison. no surprise that connect() is expensive but what is the cost of cursor()? is it cheap or expensive? -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How fast is the sqlite connection created?
Samuel R. Neff wrote: Some of this performance gain is probably related to caching data and query plan, not just opening the connection, but still that caching is connection related and is lost when you close the connection so it's a very real-world valid comparison. no surprise that connect() is expensive but what is the cost of cursor()? is it cheap or expensive? -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How fast is the sqlite connection created?
Samuel R. Neff wrote: One last set of performance numbers for opening a connection. :-) All points on the curve. ;) So the impact of open/closing connections on a real-world db really is huge. You're right, that's a huge difference. Good bit of benchmarking there. These tests with triggers is very different from tests without--are triggers completely parsed and compiled every time a connection is opened? I don't know. I'd assume that triggers were part of the "schema" and would be parsed when (or shortly after) a connection is opened. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How fast is the sqlite connection created?
One last set of performance numbers for opening a connection. :-) This is after adding in check constraints for types and foreign key triggers (which may only be used during development anyways). Non-Pooled: 17,515.6 ms Pooled with Reset :562.5 ms Pooled without Reset :109.4 ms One Connection: 62.5 ms So the impact of open/closing connections on a real-world db really is huge. These tests with triggers is very different from tests without--are triggers completely parsed and compiled every time a connection is opened? Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How fast is the sqlite connection created?
I implemented connection pooling in the .NET wrapper and using the pool it takes 90ms for 1,000 connections or 500ms if I clear out attached databases and temp tables each time. Is there a really quick way to reset attached/temp tables on a connection? Right now I do a pragma database_list; followed by a select type, name from sqlite_temp_master; and loop through the results which apparently takes 400us when there are no attached tables and temp is empty (longer if it actually has to detach/drop something). Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Samuel R. Neff [mailto:[EMAIL PROTECTED] Sent: Monday, February 26, 2007 11:58 PM To: 'sqlite-users@sqlite.org' Subject: RE: [sqlite] How fast is the sqlite connection created? I ran some tests and received fairly drastic results. Our schema has 67 tables right now (once we add in the history tables, it'll have about double that) and 116 indexes, excluding the automatic primary key indexes. I ran 1,000 simple select statements SELECT COUNT(*) FROM USERS; and the USERS table is empty (actually, the whole db is empty, only structure created for the tests). One statement per connection : 2906.3 ms One connection for all statements : 62.5 ms Some of this performance gain is probably related to caching data and query plan, not just opening the connection, but still that caching is connection related and is lost when you close the connection so it's a very real-world valid comparison. Best regards, Sam - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How fast is the sqlite connection created?
I ran some tests and received fairly drastic results. Our schema has 67 tables right now (once we add in the history tables, it'll have about double that) and 116 indexes, excluding the automatic primary key indexes. I ran 1,000 simple select statements SELECT COUNT(*) FROM USERS; and the USERS table is empty (actually, the whole db is empty, only structure created for the tests). One statement per connection : 2906.3 ms One connection for all statements : 62.5 ms Some of this performance gain is probably related to caching data and query plan, not just opening the connection, but still that caching is connection related and is lost when you close the connection so it's a very real-world valid comparison. Best regards, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: Monday, February 26, 2007 12:53 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How fast is the sqlite connection created? Samuel R. Neff wrote: > Thank you for the testing and information. ;) > When I have time to run some tests using our actual schema (120+ tables, > several hundred indexes) I'll post back here in case others are interested > in our results. From your tests it looks like more complex schemas probably > take more time to parse (as would be expected) so a real-world schema might > take longer than a test schema. Please do, the more info the merrier. I wasn't sure if anyone would find it useful but thought I'd seed the list for future reference. The schema was pretty trivial - all tables looked like "tXXX(a INT, b INT, c FLOAT, d TEXT)" and to add the indexes I just added a UNIQUE constraint to a, b & d. It would be good to have some results from a real database. Best Regards, Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How fast is the sqlite connection created?
Samuel R. Neff wrote: Thank you for the testing and information. ;) When I have time to run some tests using our actual schema (120+ tables, several hundred indexes) I'll post back here in case others are interested in our results. From your tests it looks like more complex schemas probably take more time to parse (as would be expected) so a real-world schema might take longer than a test schema. Please do, the more info the merrier. I wasn't sure if anyone would find it useful but thought I'd seed the list for future reference. The schema was pretty trivial - all tables looked like "tXXX(a INT, b INT, c FLOAT, d TEXT)" and to add the indexes I just added a UNIQUE constraint to a, b & d. It would be good to have some results from a real database. Best Regards, Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How fast is the sqlite connection created?
Martin, Thank you for the testing and information. We're just starting to use SQLite and are using SQLite.NET. This library doesn't support connection pooling and we've been discussing whether to implement connection pooling external to the library. Your post certainly makes it look worthwhile. When I have time to run some tests using our actual schema (120+ tables, several hundred indexes) I'll post back here in case others are interested in our results. From your tests it looks like more complex schemas probably take more time to parse (as would be expected) so a real-world schema might take longer than a test schema. Best regards, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: Monday, February 26, 2007 10:58 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How fast is the sqlite connection created? Martin Jenkins wrote: > So the difference in connect times between a database with 1 table and > 10 tables is ... It appears that adding indexes (and triggers?) increases the time at about the same rate as adding tables. That is a connect/first select to a database with 1 table and 3 indexes takes about as long it does to a database with 4 tables. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How fast is the sqlite connection created?
Martin Jenkins wrote: So the difference in connect times between a database with 1 table and 10 tables is ... It appears that adding indexes (and triggers?) increases the time at about the same rate as adding tables. That is a connect/first select to a database with 1 table and 3 indexes takes about as long it does to a database with 4 tables. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How fast is the sqlite connection created?
Peter van Dijk wrote:
every time you open an sqlite database file, the sqlite library has to
parse all table structures. It is much better to keep your
connection/handle open for longer periods of time.
On my XP box it takes about 220us to connect to an SQLite database from
Python, whether there is 1 table or 1000.
A "connect" here is "C=sqlite3.Connection('tmp.db'); C.close()"
0.121: To create 1 tables with 1000 rows each
0.223: To connect 1000 times (222us per connect)
0.651: To create 10 tables with 1000 rows each
0.236: To connect 1000 times (234us per connect)
5.776: To create 100 tables with 1000 rows each
0.224: To connect 1000 times (223us per connect)
58.393: To create 1000 tables with 1000 rows each
0.219: To connect 1000 times (218us per connect)
I guess the parsing is delayed until necessary because what takes the
time is, say, the first select:
A "connect" here is "C=sqlite3.Connection('tmp.db'); C.execute('select a
from t0 limit 1'); C.close()"
0.119: To create 1 tables with 1000 rows each
1.008: To connect 1000 times (1007us per connect)
0.638: To create 10 tables with 1000 rows each
1.264: To connect 1000 times (1263us per connect)
5.801: To create 100 tables with 1000 rows each
4.193: To connect 1000 times (4192us per connect)
58.419: To create 1000 tables with 1000 rows each
32.468: To connect 1000 times (32466us per connect)
So the difference in connect times between a database with 1 table and
10 tables is about 25%, fairly trivial 250us. With 100 tables connect
time is 3ms, worse by a factor of 4 but still not bad. With 1000 tables
the 33ms connect time becomes significant, but I would imagine that 1000
table databases must be pretty unusual.
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How fast is the sqlite connection created?
On Feb 25, 2007, at 10:03 AM, Alex Cheng wrote: I want to know how many time is spent when create a sqlite connection. Is it effeciency? My application creates a connection and close it when access DB everytime, is it OK? Hello Alex, every time you open an sqlite database file, the sqlite library has to parse all table structures. It is much better to keep your connection/handle open for longer periods of time. Cheers, Peter. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How fast is the sqlite connection created?
Alex Cheng wrote:
Hi,
I want to know how many time is spent when create a sqlite connection.
> Is it effeciency? My application creates a connection and close it
> when access DB everytime, is it OK?
Going by your sig, here are the times for Python 2.5 running under XP
SP2 on a 1.6GHZ dual Athlon.
import timeit
t=timeit.Timer("C=sqlite3.Connection(':memory:')", "import sqlite3")
print "%d us per connection" % (t.timeit(1) * 100/1)
90 us per connection
t=timeit.Timer("C=sqlite3.Connection('diskfile')", "import sqlite3")
print "%d us per connection" % (t.timeit(1) * 100/1)
265 us per connection
The C times will be faster, but not by much.
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] How fast is the sqlite connection created?
Hi, I want to know how many time is spent when create a sqlite connection. Is it effeciency? My application creates a connection and close it when access DB everytime, is it OK? -- powered by python
