Re: [Haskell-cafe] Takusen sqlite3 insert is very slow

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 :

On Sat, Mar 20, 2010 at 3:32 AM, Vasyl Pasternak 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