S4 development

May 31, 2010

Benchmark update

Filed under: Uncategorized — Sivert Berg @ 12:35

It turns out Erik Massop (nesciens) had some code to speedup SQL queries in his repository. Here it is compared to the old SQL code and S4 midb:
UPDATE: Erik pushed some new code, making some queries faster. The queries that saw improvement are marked in red. The rest of the queries performed the same as the old code. One change in the new code is that it keeps the SQLite connection alive instead of creating a new one for every query. The difference can best be seen on a short query like “one”:

The old code:

query_infos took: 878
session took:     827
select took:      386

Compared to the new code:

query_infos took: 432
session took:     370
select took:      365

We see that the select takes about the same time, but the whole session takes about twice as long with the old code. For large queries like “*” the difference is less visible:

The old code:

query_infos took: 228048
session took:     228024
select took:      227569

Compared to the new code:

query_infos took: 224097
session took:     224062
select took:      224052
Query SQL Old SQL Experiment S4 midb
Avg (µs) S (µs) Avg (µs) S (µs) Avg (µs) S (µs)
“one” 180115.3 ~5521.6 326.0 ~8.9 127.7 ~3.7
“*” 365540.5 ~7049.7 225482.8 ~1011.7 148165.8 ~3209.5
“artist:metallica” 6864.2 ~214.3 3755.7 ~16.7 3336.5 ~219.5
“tracknr>20” 30378.6 ~789.9 14682.2 ~214.8 1871.7 ~132.7
“tracknr>30” 27624.5 ~1372.5 10996.5 ~23.3 236.1 ~7.5
“+comment” 238729.9 ~7028.3 73566.2 ~1553.5 58894.9 ~2003.7
“tracknr:4” OR “artist~foo” AND NOT “artist:weezer” 188794.7 ~4773.8 49245.6 ~1210.9 18785.8 ~638.8

As we can see most queries are getting a nice speedup. Most notably “one” which is about 500 (!) times faster. But also “+comment” (3.2 times faster) and that long last query (3.8 times faster) gets a nice boost. The only one getting slower is “*” (taking over 2 seconds). S4 is still faster than SQL, but all the SQL queries (with the exception of “*”) are under 100 milliseconds on a moderately sized media library (~10,000 songs).

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: