Holger Freyther writes: > Hi All, > > We are using libdbi in OpenBSC to access the sqlite database and we have > experienced some performance issues. I have tested a very simple query > like "SELECT * from SMS where id = NR" and I did so with dbi and with storing > the query inside a text file and send executing cat file | sqlite3 db. > libdbi is > many many times slower than the simple setup. > > I have started to look into the DBI interface, also wrote a SystemTap probe > to see which statements are executed and I see that PRAGMA table_info > is executed for each column. > > For my testcase simply caching the last result of the PRAGMA is giving me > an almost 4x times improvement. Should I create a bug report and attach > the patches I have come up with so far. > > The code is still slower than using sqlite3 directly, is anyone else working > on figuring out why this is the case?
Hi, the speed problems result from the fact that the sqlite and sqlite3 drivers are kind of "misfits" with regard to the libdbi architecture. libdbi was designed for strongly typed databases which usually provide cheap ways (in terms of processing time) to find out the types of columns in a result set. sqlite and sqlite3 are more or less type-less (the former very much so, the latter at least distinguishes text from non-text columns). In order to work with these database engines in the context of libdbi, the drivers have to bend over backwards to assign the proper column types. SQLite doesn't make these readily available as it usually doesn't care about types. Instead, each query has to parse the CREATE TABLE statement which is the only available record of the column types. This is where you lose performance compared to accessing a SQLite database directly, as you usually know the column types in the latter case. At least you know when they're relevant. I'll have a look at the patches which you appended to your bug report. If caching is done on a per-instance base, threading should not be a problem. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 ------------------------------------------------------------------------------ Learn how Oracle Real Application Clusters (RAC) One Node allows customers to consolidate database storage, standardize their database environment, and, should the need arise, upgrade to a full multi-node Oracle RAC database without downtime or disruption http://p.sf.net/sfu/oracle-sfdevnl _______________________________________________ Libdbi-drivers-devel mailing list Libdbi-drivers-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel
