Grigorio V. Moshkin wrote:

Thank you Stefan, Romulo, and others, for help on embedded library of MonetDB! I've downloaded them, and it works, although slightly umcompletely.

Database is created via embedded_sql () call, but the following error being occured (mapi_error == MSERVER): ========= "!MALException:malInclude: could not open file: sql" ========= I set a lot of config parameters using mo_builtin_settings () / mo_add_option () / mo_system_config (). Then I call embedded_sql (). See, if I replace it by embedded_mal (), the call is successful and mapi_error == 0. What goes wrong?

This message implies that your program can not find the MAL signature files where it expects them. That's one of the issues that call for attention on Windows. You used the proper calls to set them, so I suggest to debug it to see what path actually is followed, and if the *.mal and library files are indeed where they should. Sjoerd/Romulo might add more info.

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Grigorio V. Moshkin wrote:

Thank you Martin, you were right:

...

This message implies that your program can not find the MAL signature files where it expects them. That's one of the issues that call for attention on Windows. You used the proper calls to set them, so I suggest to debug it to see what path

I guessed to download previously forgotten files: ====== http://monetdb.cwi.nl/testing/projects/monetdb/Current/sql/.Int.32.32.d-Wind... sql.mal sql_bpm.mal ====== and then placed them into my own monet_mod_path directory. Now all works!

Great! Please don't forget to make a small report on how to get embedded MonetDB on Windows it will greatly help future users. thanks again for your contribution. Martin

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

On Thu, Jan 17, 2008 at 07:35:25PM +0300, Grigorio V. Moshkin wrote:

Hi,

Our company is searching for database storage back-end to our application that provides navigation upon legal data on Russian laws and justice practice. So we will attempt test MonetDB/SQL performance amoung other database candidates. Note, read access is much more often than write access in our business model.

1. Build notes --------------

First, I tried to build MonetDB from source code provided, but it seems too difficult for me to understand instructions. My advice, if it were possible, make ready-to-build Microsoft Visual Studio projects (*.sln, *.vcproj) to compile them immediately under Windows, and include them to Windows distribution. Because it is too hard to install and configure all build tools, such as nmake, autoconfig, etc., under Windows.

So, thanks to Stefan Manegold, I have just downloaded binary libs from monetdb.cwi.nl nighty build area. It is very easy to do that using open source program WinHTTrack, I marked the following urls for (recursively) download: ================= http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/sql/.Int.32.32.d-Wind... http://monetdb.cwi.nl/testing/projects/monetdb/Current/sql/.Int.32.32.d-Wind... http://monetdb.cwi.nl/testing/projects/monetdb/Current/sql/.Int.32.32.d-Wind... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB/.Int.32.32.d-... http://monetdb.cwi.nl/testing/projects/monetdb/Current/clients/.Int.32.32.d-... =================

Then I have linked my program executable against: ================= libembeddedsql libMapi libmutils libbat libembeddedmal ================= and copied them (*.dll, *.lib) to the directory C:\monetdb5 (it is arbitrary named directory), and placed all the rest *.dll, *.lib, *.mal files to the subdirectory C:\monetdb5\libmonetdb5, and pointed "prefix", "exec_prefix" config option to C:\monetdb5, and pointed "monet_mod_path" config option to C:\monetdb5\libmonetdb5.

See my C++ code fragment in attach for all option set and call to embedded_sql.

I found, the call mapi_setAutocommit (dbh, 1) is significant, because data won't store to database, if I replace that call with mapi_setAutocommit (dbh, 0): there was no error indicated while saving data, Well with mapi_setAutocommit 0 you disable the autocommit. This is good for performance. So first attempt to increase your performance is to do

a mapi_setAutocommit(dbh, 0); mapi_query(dbh, "START TRANSACTION;"); /* now your queries */ mapi_query(dbh, "COMMIT;"); Niels

but then, data can't be loaded, if it were not saved to database.

After all, MonetDB/SQL embedded works under Windows!

The question is - performance.

I use the following Mapi calls to load/save data into database table: mapi_query, mapi_fetch_row, mapi_fetch_field, mapi_close_handle, as I found them in examles at MonetDB site.

Test for sequential create of 1000 rows with 1st field BIGINT and 2nd field CHAR (32), and 1000 rows with 1st field BIGINT and 2nd field CHAR (64) got 21 seconds. Test for random load of all 2000 the previously created objects got 0.17 seconds.

It is not the best performance amoung other candidates (such as Firebird, Empress, BerkeleyDB, etc.).

So the question is - How One Could Improve MonetDB/SQL Performance? I suppose, first, to try to use mapi_prepare once / mapi_execute many times, instead of mapy_query many times. What else?

void Database_i::init_embedded (const char* caller) { //#UC START# *478E12A603CD* LOG_D (("MONETDB Database_i::init_embedded (): caller = %s", caller)); if (s_dbh != 0) { LOG_D (("MONETDB Database_i::init_embedded (): s_dbh already initialized")); return; } opt* set = 0; int setlen = 0; if (!(setlen = mo_builtin_settings (&set))) { LOG_E (("MONETDB Database_i::init_embedded (): caller = %s: mo_builtin_settings error", caller)); throw SEA::DatabaseAPI::IOError (); } std::string monetdb_prefix = ::Core::ParamManager::instance ()->get_string ("-monetdb_prefix", "c:\\monetdb"); LOG_D (("monetdb_prefix = %s", monetdb_prefix.c_str ())); // needed to prevent the MonetDB config file from being used setlen = mo_add_option (&set, setlen, opt_config, "prefix", monetdb_prefix.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "exec_prefix", monetdb_prefix.c_str ()); //setlen = mo_add_option (&set, setlen, opt_config, "config", "W:\\build\\bin\\Purify_Static\\monetdb5.conf"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_dbinit", "include sql;"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_dbfarm", m_root_path.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "gdk_dbname", m_config.name); setlen = mo_add_option (&set, setlen, opt_config, "gdk_mem_bigsize", "262144"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_vm_minsize", "137438953472"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_alloc_map", "yes"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_embedded", "yes"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_debug", "0"); std::string monet_mod_lib = monetdb_prefix + "\\" + "libmonetdb5"; std::string monet_mod_path = monet_mod_lib + ";" + monetdb_prefix; setlen = mo_add_option (&set, setlen, opt_config, "monet_mod_path", monet_mod_path.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "monet_daemon", "yes"); setlen = mo_add_option (&set, setlen, opt_config, "monet_welcome", "yes"); std::string mero_msglog = m_root_path + "\\" + "merovingian.log"; setlen = mo_add_option (&set, setlen, opt_config, "mero_msglog", mero_msglog.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "mero_errlog", mero_msglog.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "mero_timeinterval", "600"); setlen = mo_add_option (&set, setlen, opt_config, "mero_exittimeout", "7"); std::string mal_init = monet_mod_lib + "\\" + "mal_init.mal"; setlen = mo_add_option (&set, setlen, opt_config, "mal_init", mal_init.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "mal_listing", "15"); setlen = mo_add_option (&set, setlen, opt_config, "checkpoint_dir", m_root_path.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "mapi_port", "0"); setlen = mo_add_option (&set, setlen, opt_config, "mapi_open", "false"); setlen = mo_add_option (&set, setlen, opt_config, "sql_debug", "0"); setlen = mo_add_option (&set, setlen, opt_config, "sql_logdir", m_root_path.c_str ()); std::string sql_init = monet_mod_lib + "\\" + "sql.mal"; setlen = mo_add_option (&set, setlen, opt_config, "sql_init", sql_init.c_str ()); setlen = mo_system_config (&set, setlen); LOG_D (("ACE_OS::getenv (PATH) = %s", ACE_OS::getenv ("PATH"))); s_dbh = embedded_sql (set, setlen); MapiMsg msg = mapi_error (s_dbh); if ((s_dbh == 0) || (msg != 0)) { LOG_E (("MONETDB Database_i::init_embedded (): caller = %s: mapi_error = %d", caller, (int) msg)); throw SEA::DatabaseAPI::IOError (); } MapiMsg msg1 = mapi_setAutocommit (s_dbh, 1); // significant MapiMsg msg2 = mapi_error (s_dbh); if ((msg1 != MOK) || (msg2 != 0)) { LOG_E (("MONETDB Database_i::init_embedded (): caller = %s: mapi_error msg1, msg2 = %d, %d", caller, (int) msg1, (int) msg2)); throw SEA::DatabaseAPI::IOError (); } //#UC END# *478E12A603CD* }

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl