Commit of 100k small records takes almost 2 minutes about crate

[chaudum]

hi @zopyx
what's happening in your code is that every commit requests a request, resulting in 100k requests.

you can speed up the process by doing bulk inserts. this is achieved by doing multiple adds on the same session before committing.

I've adopted your example:

import time
from datetime import datetime
import sqlalchemy as sa
from sqlalchemy import func, desc
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from crate.client import connect
from crate.client.sqlalchemy.types import Object
from crate.client.exceptions import ProgrammingError

hosts = ['localhost:4200']
engine = sa.create_engine('crate://', connect_args={'servers': hosts})
Base = declarative_base()
Session = sessionmaker(engine)
session = Session()

conn = connect(hosts)
cursor = conn.cursor()
try:
    cursor.execute('''DROP TABLE foo2''')
except ProgrammingError:
    pass
cursor.execute('''CREATE TABLE foo2 (user_id string primary key, created_at timestamp)''')


class Foo2(Base):
    __tablename__ = 'foo2'
    user_id = sa.Column(sa.String, primary_key=True)
    created_at = sa.Column(sa.DateTime)

    def __repr__(self):
        return '<Tweet {0}>'.format(self.user_id)

ts = time.time()

for i in range(100000):
    f = Foo2(user_id=i, created_at=datetime.now())
    session.add(f)
    if i % 10000 == 0:
        print(i)
    if i % 1000 == 0:
        session.commit()

print time.time() - ts
print 'done'

This one is approx twice as fast (65.69s) as your original example (125.51s).
You can still play around how many records you insert per bulk. Also note that the number of configured replicas and shards do have impact on insert times.

If you want even faster import times you should consider using the Python client directly.

e.g.

cursor.execute("""INSERT INTO foo2 (user_id, created_at) VALUES (?,?), (?, ?) ...""", [...])

see docs: https://crate.io/docs/projects/crate-python/en/latest/client.html#inserting-data

Hope this helps!
Christian