You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Lucky <wa...@163.com> on 2017/12/04 09:21:28 UTC
index about jdbc thin mode
Hi,
I got a strange thing.
I execute a query with JDBC thin mode.
But the performance and explain is very strange.
The SQL is :
SELECT FID,FNUMBER FROM "customerCache".CustomerIgniteInfo WHERE FUSEDSTATUS = 3
and explain is:
SELECT
__Z0.FID AS __C0_0,
__Z0.FNUMBER AS __C0_1
FROM "customerCache".CUSTOMERIGNITEINFO __Z0
/* "customerCache".CUSTOMERIGNITEINFO.__SCAN_ */
WHERE __Z0.FUSEDSTATUS = 3
The entity like this(I have deleted the other fields):
@Entity
@Table(name="t_customer")
public 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;
/**********Getters and Setters******************/
@Id
@GeneratedValue(generator = "personGenerator")
@GenericGenerator(name = "personGenerator", strategy = "assigned")
@Column(name = "FID")
public String getFID() {
return FID;
}
public void setFID(String id) {
this.FID = id;
}
@Column(name = "FNUMBER")
public String getFNUMBER() {
return FNUMBER;
}
public void setFNUMBER(String fNUMBER) {
FNUMBER = fNUMBER;
}
@Column(name = "FUSEDSTATUS")
public Integer getFUSEDSTATUS() {
return FUSEDSTATUS;
}
public void setFUSEDSTATUS(Integer fUSEDSTATUS) {
FUSEDSTATUS = fUSEDSTATUS;
}
}
My ignite version is 2.3.
Should this be a full table scan? It took more time.
1. I want to know why this happened.
2. How can I create a correct index?
Thanks.
Lucky
Re: index about jdbc thin mode
Posted by afedotov <al...@gmail.com>.
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: Re:Re: Re:Re: index about jdbc thin mode
Posted by afedotov <al...@gmail.com>.
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
Posted by Lucky <wa...@163.com>.
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.
Re: Re:Re: index about jdbc thin mode
Posted by afedotov <al...@gmail.com>.
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
Posted by Lucky <wa...@163.com>.
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" <al...@gmail.com> 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<Long, CustomerIgniteInfo> cache =
>ignite.getOrCreateCache(
> new CacheConfiguration<Long,
>CustomerIgniteInfo>("customerCache")
> .setIndexedTypes(Long.class, CustomerIgniteInfo.class)
> );
>
> LongStream.range(0, 10).forEach(l -> {
> cache.put(l, new CustomerIgniteInfo("FID" + l, "FNUMBER" + l,
>(int)l));
> });
>
> List<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
Posted by Lucky <wa...@163.com>.
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
Posted by afedotov <al...@gmail.com>.
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<Long, CustomerIgniteInfo> cache =
ignite.getOrCreateCache(
new CacheConfiguration<Long,
CustomerIgniteInfo>("customerCache")
.setIndexedTypes(Long.class, CustomerIgniteInfo.class)
);
LongStream.range(0, 10).forEach(l -> {
cache.put(l, new CustomerIgniteInfo("FID" + l, "FNUMBER" + l,
(int)l));
});
List<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/