Hello Dennis, Jay,
Thanks for you suggestions.
The ids are simply returned to the calling
application in a very large array. It's
the API we're writing against; so we can't
deviate from that unfortunately.
Thanks for clarifying the INSERT trigger
behavior.
I'll try your suggestions and see what
happens.
Best regards,
Kervin
--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> 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]
>
-----------------------------------------------------------------------------
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
