[Haskell-cafe] Takusen sqlite3 insert is very slow

[Vasyl Pasternak]

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
>