Hello,
I working on a function in our open-source
Outlook plugin [ http://openconnector.org ]
and I've run into some speed issues.
We've been avoiding optimizing for speed
till later but this function is currently
operating at 3 orders of magnitude worse
than acceptible and is slowing down
testing. I'd appreciate any help or
pointers optimizing the SQL in the main
loop.
I would like to get down the max number
of queries from 3 to 1, using triggers if
necessary.
The pseudo code and SQL..
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.
Any pointers would be appreciated.
Best regards,
Kervin
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
