ISSUE | SPEED TESTS | Open discussion

[v1a0]

Sqllex v0.1.10.3b

Converting (tuples) to [lists] costs for select-like methods 55.8% of all time.

This is an results for:

def select_where_1(db: SQLite3x):
    db.select(
        'main', 'id',
        WHERE={
            'name': 'Alex'
        },
        LIMIT=1000
    )

In my opinion it's too much time for such feature. Maybe we should use numpy.arrays inside sqllex's processing (instead of lists).

Found this example and i think it might be the decision.

https://stackoverflow.com/questions/63409324/why-numpy-array-is-faster-than-list-in-python

P.S.: <built-in method builtins.isinstance> takes 1/10 of all time

P.P.S:

sqllex v0.1.10.3a

crete_table(db)       # 0.003847
insert_fats(db)       # 0.06685 sec (1000 rec)
insert_slow(db)       # 0.2699 sec  (1000 rec)
insert_many_fast(db)  # 0.005199    (1000 rec)
insert_many_slow(db)  # 0.005518    (1000 rec)
select_all(db)        # 0.005709    (1000 rec)
select_where_1(db)    # 0.002922    (1000 rec)
select_where_2(db)    # 0.003836    (1000 rec) << why?

tree/dev/tests/time-tests

[v1a0]

Sqllex v0.1.10.4

The number of experimental records has been increased from 1,000 to 10,000

Running tests/time-tests/main.py

Results for running each function manually

# function call       # total runtime/(records), speedup .4/.3b
crete_table(db)       # 0.00332 sec/(1    table), 1x
insert_fats(db)       # 0.21690 sec/(10_000 rec), 3x
insert_slow(db)       # 0.92790 sec/(10_000 rec), 3x
insert_many_fast(db)  # 0.04220 sec/(10_000 rec), 1.06x
insert_many_slow(db)  # 0.04269 sec/(10_000 rec), 1.01x
select_all(db)        # 0.03394 sec/(10_000 rec), 1.68x
select_where_1(db)    # 0.02888 sec/(10_000 rec), 1.01x
select_where_2(db)    # 0.02556 sec/(10_000 rec), 1.5x

P.S.: Lister is still a problem, but logger-issue fixed.

[v1a0]

0.2.0.0-rc3, SQLite

#                     # total runtime/(records) 0.2.0.0-rc3
#                     # 0.674s
crete_table(db)       # 0.00333 sec/(1    table), 1x
insert_fats(db)       # 0.20000 sec/(10_000 rec), 1x
insert_slow(db)       # 0.69250 sec/(10_000 rec), 1x
insert_many_fast(db)  # 0.03473 sec/(10_000 rec), 1x
insert_many_slow(db)  # 0.03330 sec/(10_000 rec), 1.1x
select_all(db)        # 0.00995 sec/(10_000 rec), 1.1x
select_where_1(db)    # 0.00665 sec/(10_000 rec), 1.1x
select_where_2(db)    # 0.00699 sec/(10_000 rec), 1.22x

[v1a0]

0.2.0.0-rc3, SQLite

#                     # total runtime/(records) 0.2.0.0-rc3
#                     # 0.674s
crete_table(db)       # 0.00333 sec/(1    table), 1x
insert_fats(db)       # 0.20000 sec/(10_000 rec), 1x
insert_slow(db)       # 0.69250 sec/(10_000 rec), 1x
insert_many_fast(db)  # 0.03473 sec/(10_000 rec), 1x
insert_many_slow(db)  # 0.03330 sec/(10_000 rec), 1.1x
select_all(db)        # 0.00995 sec/(10_000 rec), 1.1x
select_where_1(db)    # 0.00665 sec/(10_000 rec), 1.1x
select_where_2(db)    # 0.00699 sec/(10_000 rec), 1.22x

Same for v0.2.0.0

[v1a0]

Speed tests moved to unit tests tests/test_sqllex.py

Now result of time tests saves as {test_name}.prof files in tests directory after run

# @unittest.skip("Turned off manually")
@unittest.skipUnless(importlib.util.find_spec('cProfile'), "Module cProfile not found")
@unittest.skipUnless(importlib.util.find_spec('pstats'), "Module pstats not found")
class TimeTestsSqllexSQLite(unittest.TestCase):

    @save_prof
    def test_create_table(self):
        ...

    @save_prof
    def test_insert_fast(self):
        ...

    ...

    @save_prof
    def test_select_where_2(self):
        ...

How to run

python -m unittest tests.test_sqllex.TimeTestsSqllexSQLite

Visualize

To visualize result from .prof files I recommend to use snakeviz module

Installation

python -m pip install snakeviz 

Runs

python -m snakeviz time_test_create_table.prof

python -m snakeviz <file_name>