Hi,
I have a C/C++ program using the sqlite db and I often do queries where I will update multiple records at a time. For example, given a table called "people" with entries "id" (an INTEGER PRIMARY KEY), "name," and "city," I'll often modify the "city" field for a group of people at once. Often, though, these people don't have consequtive ids or anything -- it's whatever the user selects in the GUI.
I have precompiled statements to the effect of "REPLACE INTO people VALUES ( ?, ?, ? );", and I just perform the replace for each entry (using the appropriate bind() commands). Now, is this faster than compiling and executing a new statement like "REPLACE INTO people VALUES ( asdf,blah,fdds ) WHERE id IN ( 3 ,7, 9, 12 );" each time? Is there a faster way?
I have the same question for SELECTing values. I have a hierarchical data structure stored in the db. When I'm reading that into the GUI, I have to do recursive searches along the lines of "SELECT * FROM groups WHERE parentId==? ;" starting with the base level 0, then for each result perform another search.
Finally, I have a third table that links people to groups. Each person can be a member of multiple groups, so the third table links the two. LinkTable = ( personId, groupId, order ) with PRIMARY KEY( personId, groupId )
Given a random list of personIds, what's the fastest way of getting the unique list of all groups they belong to? Currently, I use a precompiled statement along the lines of "SELECT * FROM LinkTable WHERE personId==?;" to get the list for each person, then use STL routines to build up a unique list. Can I do this quickly and efficiently using the SQL engine?
Thanks, - Augusto
