Message-ID: <264855a00804160847s3cbf9182ibfabff0baf57779d@mail.gmail.com>
Date: 2008-04-16T15:47:07Z
From: Sean Davis
Subject: dbSendPreparedQuery in RSQLite
In-Reply-To: <19CEF653-CA92-49BB-B557-519EC954BF3C@berkeley.edu>
On Tue, Apr 15, 2008 at 11:45 PM, James Bullard <bullard at 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 at 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 at 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
> >
>
>