[R-sig-DB] dbSendPreparedQuery in RSQLite

[Sean Davis]

On Tue, Apr 15, 2008 at 11:45 PM, James Bullard <bullard using berkeley.edu> wrote:
> Yes, in this example that would certainly work. However, I have a large
> number of queries in a tight loop and it would be nice to avoid the overhead
> of the query parsing and compiling on each call through the loop.

A couple of typical tricks for avoidance of loops is to either:

1) Use an "in" query
2) Use a temporary table (CREATE TEMPORARY TABLE .... or
dbWriteTable()) and then do a join

If you have hundreds or thousands of queries, it is quite possible
that #2 could be much faster.  Benchmarking would be useful here, but
it is definitely worth a try if you are too slow with looping.

Sean

>  thanks again, jim
>
>
>
>
>  On Apr 15, 2008, at 7:21 PM, Sean Davis wrote:
>
> > On Tue, Apr 15, 2008 at 8:31 PM, James Bullard <bullard using berkeley.edu>
> wrote:
> >
> > > How do we send prepared queries in RSQLite. We are interested in
> > > something along the lines of
> > >
> > > ps = dbSendPreparedQuery(conn, "SELECT * FROM table1 WHERE chr = ?",
> > > data.frame("integer"))
> > > for( i in 1:2)
> > >  rs = dbGetPreparedQuery(ps, i)
> > >
> > > In C it would look something along the lines of
> > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg31854.html
> > >
> >
> > It looks like this is not supported for select statements, though I
> > could easily be wrong.  In this particular case, you could rewrite
> > your select as:
> >
> > "SELECT * from table1 where chr in (1,2)"
> >
> > Sean
> >
>
>