Re: Re:Re: Re:Re: index about jdbc thin mode

[afedotov]

Hi,

Your configuration perfectly works on my side.
Make sure that you are connecting to the server node with an appropriate
configuration of indexes.

I'm afraid, that without a complete reproducer it's not possible to figure
out the reason.

Kind regards,
Alex



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re:Re: Re:Re: index about jdbc thin mode

[Lucky]

The code is here:
String sql = "explain SELECT FID,FNUMBER FROM  
\"customerCache\".CustomerIgniteInfo  WHERE FUSEDSTATUS = 3)"; 
Class.forName("org.apache.ignite.IgniteJdbcThinDriver"); Connection conn = 
DriverManager.getConnection("jdbc:ignite:thin://192.168.63.36?distributedJoins=true");
 PreparedStatement preparedStatement = conn.prepareStatement(sql);

The configuration can see the attachment.
Can transaction cause this problem?

I also use spring with my project.
package com.kingdee.ignite.common.config;

import javax.cache.configuration.MutableConfiguration;

import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.cache.CacheWriteSynchronizationMode;
import org.apache.ignite.configuration.CacheConfiguration;

import com.kingdee.ignite.common.dao.SqlCustomFunction;

public abstract class AbstractCacheConfiguration extends 
CacheConfiguration{
private static final long serialVersionUID = 6000710247906756326L;

public AbstractCacheConfiguration(){

initialization();
}

public void initialization(){
//设置默认备份节点0
setDefaultBackups();
//设置默认分区缓存
setDefaultCacheMode();
//设置默认同步备份
//等待主节点的写或者提交操作完成
//但是不等待备份节点更新完成
setDefaultWriteSynchronizationMode();
//通写
setDefaultWriteThrough();
//默认启用后写
setDefaultWriteBehindEnabled();
//默认启用通读
setDefaultReadThrough();

//后写刷新默认设置为10240
setDefaultWriteBehindFlushSize();

//每隔10秒 后写刷新
setDefaultWriteBehindFlushFrequency();
//设置持久化到数据库的记录数：1
setWriteBehindBatchSize();

setWriteBehindFlushThreadCount();

//再平衡消息的大小，设置为4M
setRebalanceBatchSize();
setIndexedTypes();
//再平衡延迟时间，设置为100 ms
setRebalanceThrottle();

//定义获取bosUid的sql函数
setSqlFunctionBosUid();


}

/**
 * 使用默认分区模式
 * @return CacheConfiguration实例
 */
public CacheConfiguration setDefaultCacheMode() {

CacheMode partitioned = CacheMode.PARTITIONED;

return super.setCacheMode(partitioned);
}

/**
 * 出于性能原因, 设置备份节点为1
 * @return CacheConfiguration实例
 */
public CacheConfiguration setDefaultBackups() {
return super.setBackups(1);
}

/**
 * 默认设置客户端节点会等待主节点的写或者
提交操纵完成，但不会等待备份节点的更新完成
 * @return CacheConfiguration实例
 */
public CacheConfiguration setDefaultWriteSynchronizationMode() {
CacheWriteSynchronizationMode primarySync = 
CacheWriteSynchronizationMode.PRIMARY_SYNC;
return super.setWriteSynchronizationMode(primarySync);
}

/**
 * 设置后写启用
 * @return CacheConfiguration实例
 */
public CacheConfiguration setDefaultWriteBehindEnabled() {
return super.setWriteBehindEnabled(true);
}

/**
 * 设置通读启用
 * @return CacheConfiguration实例
 */
public MutableConfiguration setDefaultReadThrough() {
return super.setReadThrough(true);
}

public MutableConfiguration setDefaultWriteThrough() {
return super.setWriteThrough(true);
}

/**
 * 后写缓存的最大值，如果超过了这个限值，
 * 所有的缓存数据都会被刷入缓存存储然后写缓存被清
空。
 * 如果值为0，刷新操作将会依据刷新频率间隔，
 * 注意不能将写缓存大小和刷新频率都设置为0
 * @return CacheConfiguration实例
 */
public CacheConfiguration setDefaultWriteBehindFlushSize() {
return super.setWriteBehindFlushSize(10240);
}

public CacheConfiguration setWriteBehindBatchSize() {
return super.setWriteBehindBatchSize(1);
}

public CacheConfiguration setDefaultWriteBehindFlushFrequency() {
return super.setWriteBehindFlushFrequency(1);
}

public CacheConfiguration setIndexedTypes(Class clazz){

return super.setIndexedTypes(String.class,clazz);

}

public CacheConfiguration setWriteBehindFlushThreadCount(){
return super.setWriteBehindFlushThreadCount(3);
}

public CacheConfiguration setRebalanceBatchSize(){

return super.setRebalanceBatchSize(4*1024*1024);
}

public CacheConfiguration setRebalanceThrottle(){
return super.setRebalanceThrottle(100);
}

public CacheConfiguration setSqlFunctionBosUid(){
return 

Re: Re:Re: index about jdbc thin mode

[afedotov]

Hi,

Actually, the same exact code I sent you works as expected on my side.

Could you share a reproducer so that I can take a look at it?
At least, please Ignite configuration and how you run explain query via thin
JDBC driver.

Kind regards,
Alex



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re:Re: index about jdbc thin mode

[Lucky]

afedotov,


As you say ,you removed JPA related annotations.
How to removed?


I try as your suggestion, I found if I build a new entity class and use 
cache.query() ,it will take the index, but still not take the index with JDBC 
thin mode.

What did I miss?


Thanks.







At 2017-12-04 22:27:40, "afedotov"  wrote:
>Hi,
>
>Not sure, what's wrong with your code.
>Either IX_T_BD_StatusFid or IX_T_BD_CUSTOMER should have been taken.
>
>I removed JPA related annotations and checked your code, it gave me the
>following plan:
>[[SELECT
>__Z0.FID AS __C0_0,
>__Z0.FNUMBER AS __C0_1
>FROM "customerCache".CUSTOMERIGNITEINFO __Z0
>/* "customerCache".IX_T_BD_STATUSFID: FUSEDSTATUS = 3 */
>WHERE __Z0.FUSEDSTATUS = 3], [SELECT
>__C0_0 AS FID,
>__C0_1 AS FNUMBER
>FROM PUBLIC.__T0
>/* "customerCache"."merge_scan" */]]
>
>As you can see IX_T_BD_STATUSFID index was used.
>
>I used the following code to check the case. Could you check it on your
>side?
>
>public class GroupIndexes1Main {
>
>public static class CustomerIgniteInfo implements Serializable {
>private static final long serialVersionUID = -8065741098718964203L;
>
>@QuerySqlField(index = true, orderedGroups={@QuerySqlField.Group(
>name = "IX_T_BD_StatusFid", order = 1)})
>private String FID;
>
>@QuerySqlField( orderedGroups={@QuerySqlField.Group(
>name = "IX_T_BD_StatusFid", order = 2)})
>private String FNUMBER;
>
>@QuerySqlField(orderedGroups={@QuerySqlField.Group(
>name = "IX_T_BD_CUSTOMER", order = 1),@QuerySqlField.Group(
>name = "IX_T_BD_StatusFid", order = 0)})
>private Integer FUSEDSTATUS;
>
>public CustomerIgniteInfo() {
>}
>
>public CustomerIgniteInfo(String FID, String FNUMBER, Integer
>FUSEDSTATUS) {
>this.FID = FID;
>this.FNUMBER = FNUMBER;
>this.FUSEDSTATUS = FUSEDSTATUS;
>}
>
>public String getFID() {
>return FID;
>}
>public void setFID(String id) {
>this.FID = id;
>}
>
>public String getFNUMBER() {
>return FNUMBER;
>}
>
>public void setFNUMBER(String fNUMBER) {
>FNUMBER = fNUMBER;
>}
>
>public Integer getFUSEDSTATUS() {
>return FUSEDSTATUS;
>}
>
>public void setFUSEDSTATUS(Integer fUSEDSTATUS) {
>FUSEDSTATUS = fUSEDSTATUS;
>}
>}
>
>
>public static void main(String[] args) {
>Ignite ignite = Ignition.start();
>
>IgniteCache cache =
>ignite.getOrCreateCache(
>new CacheConfigurationCustomerIgniteInfo>("customerCache")
>.setIndexedTypes(Long.class, CustomerIgniteInfo.class)
>);
>
>LongStream.range(0, 10).forEach(l -> {
>cache.put(l, new CustomerIgniteInfo("FID" + l, "FNUMBER" + l,
>(int)l));
>});
>
>List> qryResults = cache.query(
>new SqlFieldsQuery("EXPLAIN SELECT FID,FNUMBER FROM
>\"customerCache\".CustomerIgniteInfo WHERE FUSEDSTATUS = 3")
>).getAll();
>System.out.println(qryResults);
>}
>}
>
>
>
>--
>Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re:Re: index about jdbc thin mode

[Lucky]

well,
   I use jdbc thin mode.
   It's ok with cache.query();
   But It did not work with JDBC thin mode.
   Thanks.

Re: index about jdbc thin mode

[afedotov]

Hi,

Just to clarify, the same result I have while running using the thin JDBC
driver.

Class.forName("org.apache.ignite.IgniteJdbcThinDriver");


try (
Connection conn =
DriverManager.getConnection("jdbc:ignite:thin://localhost:10800");
PreparedStatement preparedStatement =
conn.prepareStatement(
"EXPLAIN SELECT FID,FNUMBER FROM
\"customerCache\".CustomerIgniteInfo WHERE FUSEDSTATUS = 3"
);
ResultSet resultSet = preparedStatement.executeQuery();
) {
while (resultSet.next())
System.out.println(resultSet.getObject(1));
}



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: index about jdbc thin mode

[afedotov]

Hi,

Not sure, what's wrong with your code.
Either IX_T_BD_StatusFid or IX_T_BD_CUSTOMER should have been taken.

I removed JPA related annotations and checked your code, it gave me the
following plan:
[[SELECT
__Z0.FID AS __C0_0,
__Z0.FNUMBER AS __C0_1
FROM "customerCache".CUSTOMERIGNITEINFO __Z0
/* "customerCache".IX_T_BD_STATUSFID: FUSEDSTATUS = 3 */
WHERE __Z0.FUSEDSTATUS = 3], [SELECT
__C0_0 AS FID,
__C0_1 AS FNUMBER
FROM PUBLIC.__T0
/* "customerCache"."merge_scan" */]]

As you can see IX_T_BD_STATUSFID index was used.

I used the following code to check the case. Could you check it on your
side?

public class GroupIndexes1Main {

public static class CustomerIgniteInfo implements Serializable {
private static final long serialVersionUID = -8065741098718964203L;

@QuerySqlField(index = true, orderedGroups={@QuerySqlField.Group(
name = "IX_T_BD_StatusFid", order = 1)})
private String FID;

@QuerySqlField( orderedGroups={@QuerySqlField.Group(
name = "IX_T_BD_StatusFid", order = 2)})
private String FNUMBER;

@QuerySqlField(orderedGroups={@QuerySqlField.Group(
name = "IX_T_BD_CUSTOMER", order = 1),@QuerySqlField.Group(
name = "IX_T_BD_StatusFid", order = 0)})
private Integer FUSEDSTATUS;

public CustomerIgniteInfo() {
}

public CustomerIgniteInfo(String FID, String FNUMBER, Integer
FUSEDSTATUS) {
this.FID = FID;
this.FNUMBER = FNUMBER;
this.FUSEDSTATUS = FUSEDSTATUS;
}

public String getFID() {
return FID;
}
public void setFID(String id) {
this.FID = id;
}

public String getFNUMBER() {
return FNUMBER;
}

public void setFNUMBER(String fNUMBER) {
FNUMBER = fNUMBER;
}

public Integer getFUSEDSTATUS() {
return FUSEDSTATUS;
}

public void setFUSEDSTATUS(Integer fUSEDSTATUS) {
FUSEDSTATUS = fUSEDSTATUS;
}
}


public static void main(String[] args) {
Ignite ignite = Ignition.start();

IgniteCache cache =
ignite.getOrCreateCache(
new CacheConfiguration("customerCache")
.setIndexedTypes(Long.class, CustomerIgniteInfo.class)
);

LongStream.range(0, 10).forEach(l -> {
cache.put(l, new CustomerIgniteInfo("FID" + l, "FNUMBER" + l,
(int)l));
});

List> qryResults = cache.query(
new SqlFieldsQuery("EXPLAIN SELECT FID,FNUMBER FROM
\"customerCache\".CustomerIgniteInfo WHERE FUSEDSTATUS = 3")
).getAll();
System.out.println(qryResults);
}
}



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/