[R-sig-DB] dbSendPreparedQuery in RSQLite
Sean Davis
@d@v|@2 @end|ng |rom m@||@n|h@gov
Wed Apr 16 17:47:07 CEST 2008
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
> >
>
>
More information about the R-sig-DB
mailing list