From fcfc590238769c0ceba0e7bd03a0b98eb2bef69b Mon Sep 17 00:00:00 2001 From: =?utf8?q?Massimo=20Cor=C3=A0?= Date: Fri, 9 Jul 2010 00:15:03 +0200 Subject: [PATCH] symbol-db: added sqlite benchmark. Sqlite APIs are 10 times faster than libgda's. From the tests I get 0.78 seconds for 20k symbols with sqlite and 7.5 seconds with libgda's. Am I missing something or is libgda so slow? --- plugins/symbol-db/benchmark/libgda/libgda.c | 18 +-- plugins/symbol-db/benchmark/sqlite/Makefile.am | 32 +++++ plugins/symbol-db/benchmark/sqlite/sqlite.c | 179 +++++++++++++++++++++++++ 3 files changed, 220 insertions(+), 9 deletions(-) create mode 100644 plugins/symbol-db/benchmark/sqlite/Makefile.am create mode 100644 plugins/symbol-db/benchmark/sqlite/sqlite.c diff --git a/plugins/symbol-db/benchmark/libgda/libgda.c b/plugins/symbol-db/benchmark/libgda/libgda.c index c47a46558..ba7310fa0 100644 --- a/plugins/symbol-db/benchmark/libgda/libgda.c +++ b/plugins/symbol-db/benchmark/libgda/libgda.c @@ -97,8 +97,7 @@ get_insert_statement_by_query_id (GdaSet **plist) "type:gchararray */, ## /* name:'typename' type:gchararray */)"; /* create a new GdaStatement */ - stmt = gda_sql_parser_parse_string (sql_parser, sql, NULL, - NULL); + stmt = gda_sql_parser_parse_string (sql_parser, sql, NULL, NULL); if (gda_statement_get_parameters ((GdaStatement*)stmt, plist, NULL) == FALSE) @@ -263,7 +262,7 @@ void create_table (GdaConnection *cnc) { run_sql_non_select (cnc, "DROP table IF EXISTS sym_type"); - run_sql_non_select (cnc, "CREATE TABLE sym_type (type_id integer PRIMARY KEY AUTOINCREMENT," + run_sql_non_select (cnc, "CREATE TABLE sym_type (type_id integer PRIMARY KEY AUTOINCREMENT," "type_type text not null," "type_name text not null," "unique (type_type, type_name))"); @@ -295,6 +294,13 @@ insert_data (GdaConnection *cnc) gint queue_length = g_queue_get_length (values_queue); + if ((stmt = get_insert_statement_by_query_id (&plist)) + == NULL) + { + g_warning ("query is null"); + return; + } + g_message ("populating transaction.."); for (i = 0; i < queue_length; i++) { @@ -302,12 +308,6 @@ insert_data (GdaConnection *cnc) gchar **tokens = g_strsplit (value, "|", 2); GdaSet *last_inserted = NULL; - if ((stmt = get_insert_statement_by_query_id (&plist)) - == NULL) - { - g_warning ("query is null"); - return; - } /* type parameter */ if ((param = gda_set_get_holder ((GdaSet*)plist, "type")) == NULL) diff --git a/plugins/symbol-db/benchmark/sqlite/Makefile.am b/plugins/symbol-db/benchmark/sqlite/Makefile.am new file mode 100644 index 000000000..2a597777a --- /dev/null +++ b/plugins/symbol-db/benchmark/sqlite/Makefile.am @@ -0,0 +1,32 @@ +noinst_PROGRAMS = \ + benchmark-sqlite + + +AM_CPPFLAGS = $(LIBANJUTA_CFLAGS) \ + $(PLUGIN_SYMBOL_DB_CFLAGS) \ + -DDEBUG + +benchmark_sqlite_SOURCES = \ + sqlite.c + +benchmark_sqlite_LDFLAGS = \ + $(LIBANJUTA_LIBS) \ + $(ANJUTA_LIBS) \ + $(PLUGIN_SYMBOL_DB_LIBS) \ + `pkg-config --libs sqlite3` + +benchmark_libgda_LDADD = ../../libanjuta-symbol-db.la + + +## File created by the gnome-build tools + + + + + + + + + + +-include $(top_srcdir)/git.mk diff --git a/plugins/symbol-db/benchmark/sqlite/sqlite.c b/plugins/symbol-db/benchmark/sqlite/sqlite.c new file mode 100644 index 000000000..18fe4c150 --- /dev/null +++ b/plugins/symbol-db/benchmark/sqlite/sqlite.c @@ -0,0 +1,179 @@ +#include +#include +#include +#include +#include +#include + +#define HASH_VALUES_FILE "../data/hash_values.log" +#define DB_FILE "example_db.db" + +GQueue *values_queue; + +static void +create_table (sqlite3 *db) +{ + gchar *sql = "CREATE TABLE sym_type (type_id integer PRIMARY KEY AUTOINCREMENT," + "type_type text not null," + "type_name text not null," + "unique (type_type, type_name))" ; + + sqlite3_exec(db, sql, NULL, 0, NULL); + +} + +static void +delete_previous_db () +{ + GFile *file; + + g_message ("deleting file "DB_FILE"..."); + + file = g_file_new_for_path (DB_FILE); + + g_file_delete (file, NULL, NULL); + + g_object_unref (file); + + g_message ("..OK"); +} + +static gint +open_connection (sqlite3 **db) +{ + gint rc = 0; + sqlite3_open (DB_FILE, &(*db)); + + if (rc) + { + g_message ("Can't open database: %s\n", sqlite3_errmsg(*db)); + sqlite3_close(*db); + return -1; + } + return 0; +} + +static void +load_queue_values () +{ + gchar line[80]; + values_queue = g_queue_new (); + + FILE *file = fopen (HASH_VALUES_FILE, "r"); + + while( fgets(line,sizeof(line),file) ) + { + /*g_message ("got %s", line);*/ + g_queue_push_tail (values_queue, g_strdup (line)); + } + + fclose (file); +} + +static void +insert_data (sqlite3 *db) +{ + sqlite3_stmt *stmt; + gint i; + gdouble elapsed_DEBUG; + GTimer *sym_timer_DEBUG = g_timer_new (); + + gchar *sql_str = "INSERT INTO sym_type (type_type, type_name) VALUES (?, ?)"; + + gint queue_length = g_queue_get_length (values_queue); + + g_message ("begin transaction..."); + sqlite3_exec(db, "BEGIN", 0, 0, 0); + g_message ("..OK"); + + g_message ("populating transaction.."); + for (i = 0; i < queue_length; i++) + { + gchar * value = g_queue_pop_head (values_queue); + gchar **tokens = g_strsplit (value, "|", 2); + + if ( sqlite3_prepare(db, + sql_str, // stmt + -1, // If than zero, then stmt is read up to the first nul terminator + &stmt, + 0 /* Pointer to unused portion of stmt */ + ) != SQLITE_OK) + { + printf("\nCould not prepare statement."); + return; + } + + if (sqlite3_bind_text( + stmt, + 1, /* Index of wildcard */ + tokens[0], + strlen (tokens[0]), + SQLITE_STATIC) != SQLITE_OK) + { + printf("\nCould not bind int.\n"); + return; + } + + if (sqlite3_bind_text( + stmt, + 2, /* Index of wildcard */ + tokens[1], + strlen (tokens[1]), + SQLITE_STATIC) != SQLITE_OK) + { + printf("\nCould not bind int.\n"); + return; + } + + if (sqlite3_step(stmt) != SQLITE_DONE) { + printf("\nCould not step (execute) stmt.\n"); + return; + } + + g_strfreev(tokens); + + sqlite3_reset(stmt); + } + elapsed_DEBUG = g_timer_elapsed (sym_timer_DEBUG, NULL); + g_message ("..OK (elapsed %f)", elapsed_DEBUG); + + g_message ("committing..."); + + sqlite3_exec(db, "COMMIT", 0, 0, 0); + + elapsed_DEBUG = g_timer_elapsed (sym_timer_DEBUG, NULL); + g_message ("..OK (elapsed %f)", elapsed_DEBUG); +} + +gint +main(gint argc, gchar **argv) +{ + sqlite3 *db = NULL; + + + if ( !g_thread_supported() ) + g_thread_init( NULL ); + + g_type_init(); + + + delete_previous_db (); + + if (open_connection (&db) < 0) + return -1; + + create_table (db); + + /* load $ wc -l hash_values.log + * 20959 hash_values.log + * into our queue. + */ + load_queue_values (); + + + insert_data (db); + + + sqlite3_close(db); + return 0; +} -- 2.11.4.GIT