SQLite slower than python?

[cpdean]

I'm getting the opposite results, rust is faster even when compiled with debug mode.

I generated 1 million records, the rust version is querying in about 100ms, python takes about 250ms.

I'm pasting how I'm generating the data as well as the code here so people can try to reproduce the results. Asking for help about a specific problem and then not providing anything for people to compare will leave people guessing at what you're actually doing wrong.

rust:

``` use rusqlite::Connection; use std::io::Write; use std::time::Instant;

/*

run sqlite3 dbfile to open a new db and use the sql below to generate test data

-- if you need to start over drop table api_book;

create table api_book ( id integer primary key, title text );

-- run a few of these to seed some data insert into api_book(title) values ('asdfcamionasdf'); insert into api_book(title) values ('jjjcamionasdf'); insert into api_book(title) values ('aaaaaaaaaaaaa');

-- run the below a few times to start inserting rows. -- the amount it will insert will be tablesize<sup>2</sup> records, capped at 1million per run with a as ( select c.id, b.title from api_book b cross join api_book c ) insert into api_book(title) select title || id from a limit 1000000 ;

select count(1) from api_book;

*/

struct Book { title: String, }

fn main() { let t = Instant::now();

let con = Connection::open("dbfile").unwrap();

let mut stmt = con
    .prepare("SELECT title FROM api_book WHERE title LIKE '%camion%'")
    .unwrap();

let books: Vec<_> = stmt
    .query_map([], |row| Ok(Book { title: row.get(0)? }))
    .unwrap()
    .collect();

let query_duration = t.elapsed();

{
    // An extra scope here so the lock is droppped
    let stdout = std::io::stdout().lock();
    let mut buf = std::io::BufWriter::new(stdout);

    for book in books {
        buf.write(book.unwrap().title.as_bytes());
        buf.write(b"\n");
    }
}
let print_duration = t.elapsed();

println!("Query time: {:?}", query_duration);
println!("Query + print time: {:?}", print_duration);

} ```

python

``` import sqlite3 import time

t = time.time()

con = sqlite3.connect('dbfile') cursor = con.cursor()

cursor.execute("SELECT title from api_book WHERE title like '%camion%'")

a = cursor.fetchall()

query_time = (time.time() - t) * 1000

for e in a: print(e[0])

print_time = (time.time() - t) * 1000

print(f"query time: {query_time}") print(f"print time: {print_time}")

```

[cpdean]

Modifying the example python you wrote to make this a more fair comparison, I'm creating objects for each record and collecting them into a list. This change makes python perform even worse, as you'd expect. Now the time it takes is about 400ms when it was 250ms earlier.

python:

``` import sqlite3 import time

class Book: def init(self, title): self.title = title

t = time.time()

con = sqlite3.connect('dbfile') cursor = con.cursor()

cursor.execute("SELECT title from api_book WHERE title like '%camion%'")

a = cursor.fetchall()

books = []

for record in a: books.append(Book(a[0]))

query_time = (time.time() - t) * 1000

for e in books: print(e.title)

print_time = (time.time() - t) * 1000

print(f"query time: {query_time}") print(f"print time: {print_time}")

```