You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Biraj Deb <bi...@decisionmanagementsolutions.com> on 2023/01/06 07:22:21 UTC
Text Search from Ignite Cache
Hi all,
What is the best way to search partial text and ignore cases from a table
to get best performance?
Our backend code in *Java. *Our environment has RAM: 61 gib, cpu: 16
vcpus, core : 8.
I have tried to index on that field and I'm still getting results in 4
sec.
Query Is :
*SELECT OBJ.PROP_VALUE_STR1 as name FROM DFM.BRANCH B INNER JOIN DFM.OBJECT
OBJ ON 1=1 WHERE (OBJ.BRANCH_ID = B.ID <http://B.ID>) AND
((CAST(OBJ.PROP_VALUE_STR1 AS VARCHAR_IGNORECASE) LIKE 'qualify%') AND
((OBJ.ID <http://OBJ.ID> <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
((OBJ.REFERENCE_ID <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
((B.PROJECT_ID = 'cad78e78-a38b-4340-817b-d689b988ec17') AND
(OBJ.OBJECT_TYPE_ID =1)))))*
In above Query join two table in that *OBJECT* table has index on
BRANCH_ID and PROP_VALUE_STR1 and *BRANCH* table has index on
PROJECT_ID. And Object table has more than a million entry
Re: Text Search from Ignite Cache
Posted by John Heitor <jo...@heitorprojects.com>.
Please unsubscribe me from your email list.
On Fri, 06 Jan 2023, 09:32 Pavel Tupitsyn <pt...@apache.org> wrote:
> Ignite has full text search capabilities (based on Lucene engine) with
> TextQuery [1] that should perform better for partial matches.
> Example: [2]
>
> [1]
> https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/cache/query/TextQuery.html
> [2]
> https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java#L147
>
> On Fri, Jan 6, 2023 at 9:22 AM Biraj Deb <
> birajdeb@decisionmanagementsolutions.com> wrote:
>
>> Hi all,
>> What is the best way to search partial text and ignore cases from a table
>> to get best performance?
>> Our backend code in *Java. *Our environment has RAM: 61 gib, cpu: 16
>> vcpus, core : 8.
>> I have tried to index on that field and I'm still getting results in 4
>> sec.
>> Query Is :
>> *SELECT OBJ.PROP_VALUE_STR1 as name FROM DFM.BRANCH B INNER JOIN
>> DFM.OBJECT OBJ ON 1=1 WHERE (OBJ.BRANCH_ID = B.ID <http://B.ID>) AND
>> ((CAST(OBJ.PROP_VALUE_STR1 AS VARCHAR_IGNORECASE) LIKE 'qualify%') AND
>> ((OBJ.ID <http://OBJ.ID> <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
>> ((OBJ.REFERENCE_ID <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
>> ((B.PROJECT_ID = 'cad78e78-a38b-4340-817b-d689b988ec17') AND
>> (OBJ.OBJECT_TYPE_ID =1)))))*
>> In above Query join two table in that *OBJECT* table has index on
>> BRANCH_ID and PROP_VALUE_STR1 and *BRANCH* table has index on
>> PROJECT_ID. And Object table has more than a million entry
>>
>>
>>
>>
>>
Re: Text Search from Ignite Cache
Posted by Biraj Deb <bi...@decisionmanagementsolutions.com>.
Hello Pavel
thanks for your quick response.
I have tried from your given examples TextQuery , IndexQuery this will
help to search on a single table i need to join two tables then search text
based on condition. with this requirement how we able to search
with Lucene engine
On Fri, Jan 6, 2023 at 1:02 PM Pavel Tupitsyn <pt...@apache.org> wrote:
> Ignite has full text search capabilities (based on Lucene engine) with
> TextQuery [1] that should perform better for partial matches.
> Example: [2]
>
> [1]
> https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/cache/query/TextQuery.html
> [2]
> https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java#L147
>
> On Fri, Jan 6, 2023 at 9:22 AM Biraj Deb <
> birajdeb@decisionmanagementsolutions.com> wrote:
>
>> Hi all,
>> What is the best way to search partial text and ignore cases from a table
>> to get best performance?
>> Our backend code in *Java. *Our environment has RAM: 61 gib, cpu: 16
>> vcpus, core : 8.
>> I have tried to index on that field and I'm still getting results in 4
>> sec.
>> Query Is :
>> *SELECT OBJ.PROP_VALUE_STR1 as name FROM DFM.BRANCH B INNER JOIN
>> DFM.OBJECT OBJ ON 1=1 WHERE (OBJ.BRANCH_ID = B.ID <http://B.ID>) AND
>> ((CAST(OBJ.PROP_VALUE_STR1 AS VARCHAR_IGNORECASE) LIKE 'qualify%') AND
>> ((OBJ.ID <http://OBJ.ID> <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
>> ((OBJ.REFERENCE_ID <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
>> ((B.PROJECT_ID = 'cad78e78-a38b-4340-817b-d689b988ec17') AND
>> (OBJ.OBJECT_TYPE_ID =1)))))*
>> In above Query join two table in that *OBJECT* table has index on
>> BRANCH_ID and PROP_VALUE_STR1 and *BRANCH* table has index on
>> PROJECT_ID. And Object table has more than a million entry
>>
>>
>>
>>
>>
Re: Text Search from Ignite Cache
Posted by Pavel Tupitsyn <pt...@apache.org>.
Ignite has full text search capabilities (based on Lucene engine) with
TextQuery [1] that should perform better for partial matches.
Example: [2]
[1]
https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/cache/query/TextQuery.html
[2]
https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java#L147
On Fri, Jan 6, 2023 at 9:22 AM Biraj Deb <
birajdeb@decisionmanagementsolutions.com> wrote:
> Hi all,
> What is the best way to search partial text and ignore cases from a table
> to get best performance?
> Our backend code in *Java. *Our environment has RAM: 61 gib, cpu: 16
> vcpus, core : 8.
> I have tried to index on that field and I'm still getting results in 4
> sec.
> Query Is :
> *SELECT OBJ.PROP_VALUE_STR1 as name FROM DFM.BRANCH B INNER JOIN
> DFM.OBJECT OBJ ON 1=1 WHERE (OBJ.BRANCH_ID = B.ID <http://B.ID>) AND
> ((CAST(OBJ.PROP_VALUE_STR1 AS VARCHAR_IGNORECASE) LIKE 'qualify%') AND
> ((OBJ.ID <http://OBJ.ID> <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
> ((OBJ.REFERENCE_ID <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND
> ((B.PROJECT_ID = 'cad78e78-a38b-4340-817b-d689b988ec17') AND
> (OBJ.OBJECT_TYPE_ID =1)))))*
> In above Query join two table in that *OBJECT* table has index on
> BRANCH_ID and PROP_VALUE_STR1 and *BRANCH* table has index on
> PROJECT_ID. And Object table has more than a million entry
>
>
>
>
>