[ https://issues.apache.org/jira/browse/PHOENIX-3133?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Lars Hofhansl updated PHOENIX-3133: ----------------------------------- Fix Version/s: (was: 4.8.1) 4.8.2 4.9.0 no patch. so moving to 4.8.2 > Investigate why offset queries with reverse scan take a long time > ----------------------------------------------------------------- > > Key: PHOENIX-3133 > URL: https://issues.apache.org/jira/browse/PHOENIX-3133 > Project: Phoenix > Issue Type: Bug > Reporter: Samarth Jain > Assignee: Ankit Singhal > Fix For: 4.9.0, 4.8.2 > > > We need to workaround HBASE-16296 because users of Phoenix won't see the fix > until at least the fix makes it into a release version of HBase. > Unfortunately, often times users are forced to stick to earlier version of > HBase, even after a release. PHOENIX-3121 works around the issue when there's > only a LIMIT clause. However, if there's a LIMIT and an OFFSET, the issue > still occurs. > Repro code courtesy, [~mujtabachohan] > {code} > DDL: > CREATE TABLE IF NOT EXISTS XYZ.T ( > TENANT_ID CHAR(15) NOT NULL, > KEY_PREFIX CHAR(3) NOT NULL, > CREATED_DATE DATE, > CREATED_BY CHAR(15), > LAST_UPDATE DATE, > LAST_UPDATE_BY CHAR(15), > SYSTEM_MODSTAMP DATE > CONSTRAINT PK PRIMARY KEY ( > TENANT_ID, > KEY_PREFIX > ) > ) VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true, > REPLICATION_SCOPE=1 > > CREATE VIEW IF NOT EXISTS XYZ.ABC_VIEW ( > ACTIVITY_DATE DATE NOT NULL, > WHO_ID CHAR(15) NOT NULL, > WHAT_ID CHAR(15) NOT NULL, > CHANNEL_TYPE VARCHAR NOT NULL, > CHANNEL_ACTION_TYPE VARCHAR NOT NULL, > ENGAGEMENT_HISTORY_POC_ID CHAR(15) , > CHANNEL_CONTEXT VARCHAR > CONSTRAINT PKVIEW PRIMARY KEY > ( > ACTIVITY_DATE, WHO_ID, WHAT_ID, CHANNEL_TYPE, > CHANNEL_ACTION_TYPE > ) > ) > AS SELECT * FROM XYZ.T WHERE KEY_PREFIX = '08m' > UPSERT records using this: > Connection con = > DriverManager.getConnection("jdbc:phoenix:samarthjai-ltm3.internal.salesforce.com", > new Properties()); > PreparedStatement pStatement; > pStatement = con.prepareStatement("upsert into XYZ.ABC_VIEW > (ACTIVITY_DATE,CHANNEL_ACTION_TYPE,CHANNEL_TYPE,TENANT_ID,WHAT_ID,WHO_ID) > values (TO_DATE('2010-11-11 > 00:00:00.000'),?,'ABC','00Dx0000000GyYS','701x00000000dzp','00Qx0000001S2qa')"); > for (int i=0; i<10000000;i++) { > pStatement.setString(1, UUID.randomUUID().toString()); > pStatement.execute(); > > if (i % 10000 == 0) { > con.commit(); > System.out.println(i); > } > } > Sample query: > @Test > public void testLimitCacheQuery() throws Exception { > String url = "jdbc:phoenix:localhost:2181"; > try (Connection conn = DriverManager.getConnection(url)) { > PreparedStatement stmt = conn.prepareStatement("select * from > XYZ.ABC_VIEW where who_id = '00Qx0000001S2qa' and TENANT_ID='00Dx0000000GyYS' > order by activity_date desc LIMIT 18 OFFSET 2"); > stmt.setFetchSize(10); > try (ResultSet rs = stmt.executeQuery()) { > long startTime = System.currentTimeMillis(); > int record = 0; > while (rs.next()) { > System.out.println("Record "+ (++record) + " Time: " + > (System.currentTimeMillis() - startTime)); > startTime = System.currentTimeMillis(); > } > } > } > } > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)