[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Optimizing an insert/update
From:       Dennis Cote <dennis.cote () gmail ! com>
Date:       2008-02-29 16:47:53
Message-ID: 47C83739.5000907 () gmail ! com
[Download RAW message or body]

Michael Miller wrote:
> I apologize if this is a double-post; I just got approved for the mailing
> list, and I can't find the older message in the archives, so I'm reposting
> it.
> 
> I have a table with two columns, the first with a string and the second with
> an integer.
> 
> 
> Given a set of input strings, I want to perform this operation 50,000+ times
> preferably in a single transaction: "If the string doesn't exist in the
> table, create a new row with the string in the first column and 1 in the
> second column. If the string does exist in the table, increment the second
> column by 1"
> 
> 
> What I am doing now is using the UPDATE statement, and checking (via C#) the
> number of rows affected, and if this is zero, running an insert statement.
> Doing this 50,000 times, without an encapsulating transaction, is an
> expensive operation.
> 
> 
> Is there any way to encapsulate this into SQL so that no outside interaction
> in C# is needed to perform this operation, and so I can put 50,000 of these
> into a transaction and commit all at once?
> 
> 
> Thanks,
> 
> Mike
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

See this thread from last week 
http://article.gmane.org/gmane.comp.db.sqlite.general/35829/match=easy+question+concerning+c%2b%2b+sqlite3


This exact issue was discussed.

You can prepare the query once, and execute it 50K times in a 
transaction to quickly enter all your words.

HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[prev in list] [next in list] [prev in thread] [next in thread]