[Haskell-cafe] Takusen sqlite3 insert is very slow

Jason Dagit dagit at codersbase.com
Sat Mar 20 12:36:53 EDT 2010

On Sat, Mar 20, 2010 at 3:32 AM, Vasyl Pasternak
<vasyl.pasternak at gmail.com>wrote:

> Hi Cafe,
> I have another problem, please look at code:
> storeInDb = withSession (connect "test.db")
>             (do
>                 execDDL (sql "create table x (y int)")
>                 forM_ ([1..10000] :: [Int])
>                   (\x -> do
>                       execDML (cmdbind ("insert into x (y) values (?);")
>                                [bindP x])
>                       return ()))
> This code runs 16 seconds which is very slow for this simple task. RTS
> output is below. After profiling this program I found that 85% of its
> time it spends in  'Database.Sqlite.SqliteFunctions.stmtFetch'.
> Currently I don't know how to make it faster, maybe anyone had this
> problem later?
> HDBC inserts very fast, so this is not sqlite error.

Can you show the HDBC version?  Maybe they make different assumptions about
transactions or fetching the number of affected rows?

If I'm reading the source of takusen correctly it's using a different
transaction for each insert and stmtFetch is getting called to return the
number of rows inserted.  Which should be 1 every time and for your
purposes, ignorable.  You should be able to change to execDDL, but I
seriously doubt that will have any impact on performance.  It looks like the
only difference between execDDL and execDML is that execDDL has ">> return
()" at the end of it.

You might try running your inserts inside withTransaction.  The default
behavior of sqlite is to use a separate transaction for each statement.
 Perhaps this is adding overhead that shows up during stmtFetch.

How long does your HDBC version take?  Is it a factor of 10?  Factor of 2?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.haskell.org/pipermail/haskell-cafe/attachments/20100320/4f77e8f7/attachment.html

More information about the Haskell-Cafe mailing list