[Haskell-cafe] Takusen sqlite3 insert is very slow

[Jason Dagit]

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?

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