Kervin L. Pierre wrote:
I'd appreciate any help or
pointers optimizing the SQL in the main
loop.
for( 'large number' ){
// query1
"SELECT id FROM table
WHERE attr1 = 'a', attr2 = 'b', ..."
if( 'query1 returns no rows' ){
if( 'create flag is on' ){
// query2
"INSERT OR REPLACE values
( attr1 = 'a', attr2 = 'b', ..."
// query3
"SELECT last_insert_rowid()"
// use returned id ...
}
}
else{ // use returned id ... }
}
Basically, for every iteration we check
that an id exists ( ie. 'query1' ), if
it does we use it. But if the id does
not exist we insert a row ( ie. 'query2'
), then get the 'last_insert_rowid'
(ie. 'query3' ) and use that.
Can this be done using 1 query and
possibly an insert trigger?
I've tried adding...
"CREATE TRIGGER mytrigger
AFTER INSERT ON mytable
BEGIN
SELECT id from mytable
WHERE attr1 = new.attr1
AND attr2 = new.attr2;
END"
...and then planned on changing 'query2'
to "INSERT OR IGNORE..."
But this does not work as sqlite3_step()
doesn't seem to ever return SQLITE_ROW
on the INSERT, even after the trigger
executes the SELECT.
Kervin,
Sqlite_step will never return SQLITE_ROW from an INSERT query. Your
insert is executing the select query inside your trigger and then
throwing the result away because your trigger doesn't use the result for
anything.
What do you want to do with the id returned by the select in the
trigger? You will need to add SQL to do whatever that is inside the
trigger to get this to work.
I suspect that your speed issue is primarily caused by your "large
number" of executions of the select you call query 1 without suitable
indexes, which causes you to execute a large number of table scans
through a table that contains a large number of rows. If this is the
case adding a index on attr1 and attr2 should help immensely.
create index table_attr1_attr2 on table(attr1, atttr2);
Also, your pseudo-code doesn't say if you are preparing your statements
once before your loop or if your are preparing the statements before
each execution. If you are not preparing your statements outside the
loop and binding the parameters, you could change that to eliminate a
"large number" of unnecessary recompilations of the same SQL statements.
It's not clear to me what you are trying to do in this loop but if you
can elaborate on your explanation, we may be able to suggest a much
faster way to accomplish your goal.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
