Hi James, Thanks for the quick reply. No I hadn't tried using the small query hint but I will try that tomorrow. I have taken a look at PHOENIX-1267 and PHOENIX-1711. If the query hint makes a big difference I will look into 1267 but I would really like to remove my hacky PreparedStatementCache and do it properly.
Is the best thing for me to put together an outline patch and submit it to either this list or 1711? Then you can give me some feedback on the approach? Thanks, Ben On 2018/01/29 17:17:44, James Taylor <j...@apache.org> wrote: > Hi Ben,> > Have you tried using the SMALL hint in your queries: SELECT /*+ SMALL */> > ...? This is reported to improve point lookups quite a bit. FYI, we also> > have PHOENIX-1267 which would be a good newbie JIRA to implement.> > > I also have a patch over on PHOENIX-1711 that caches the MutationPlan> > (executable output of compilation) that might be a reasonable approach for> > optimizing point lookups as well. The idea would be that a prepared> > statement would hold on to its QueryPlan/MutationPlan and not have to> > recompile every time execute is called. The patch unfortunately never was> > committed.> > > Thanks,> > James> > > On Mon, Jan 29, 2018 at 1:59 AM, Ben Gilbert <be...@gmail.com> wrote:> > > > While testing locally I have spotted some areas I would like to change for> > > what could be significant performance improvements.> > >> > > One of our use cases is bulk fetching many rows (10_000) by rowkey with> > > 'select x where rowkey in (:ids)' statements. (The ids from an external> > > source).> > >> > > When doing this 25% of the time is spent in:> > >> > > org.apache.phoenix.jdbc.PhoenixPreparedStatement.PhoenixPreparedStatement(> > > PhoenixConnection,> > > String)> > > org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(String)> > >> > > I have worked round this temporarily by writing a PreparedStatementCache> > > and copying fields using reflection. This is somewhat ugly and I would like> > > to submit a pull request with caching of the CompilableStatement. Any> > > preferences/ thoughts of how I should implement this?> > >> > > As a note after the PreparedStatementCache was put in I am still seeing 14%> > > of the time in org.apache.phoenix.compile.QueryCompiler.compile() but this> > > looks much harder to improve.> > >> >
