[Haskell-cafe] Takusen sqlite3 insert is very slow

[David Sankel]

This FAQ on the sqlite website seems relevant:

http://www.sqlite.org/faq.html#q19

David

On Wed, Mar 24, 2010 at 5:34 PM, Vasyl Pasternak
<vasyl.pasternak at gmail.com>wrote:

> Hi Jason,
>
> Your recommendations worked for me. When I enclosed updating into
> single transaction, the code executed in less than 0.5 seconds, which
> is as fast as HDBC version. I didn't go deeper, hoping, that
> everything will be OK from now.
>
> Thank you,
> Vasyl
>
> 2010/3/20 Jason Dagit <dagit at codersbase.com>:
> >
> >
> > 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
> >
> _______________________________________________
> Haskell-Cafe mailing list
> Haskell-Cafe at haskell.org
> http://www.haskell.org/mailman/listinfo/haskell-cafe
>



-- 
David Sankel
Sankel Software
www.sankelsoftware.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.haskell.org/pipermail/haskell-cafe/attachments/20100326/a96f8305/attachment.html