Query execution is too long

[siva]

Hi,
I have .Net Client And Server App using Ignite V2.9.1.

I have cache Name COMMONFORMSSCHEMA and cache created using Model class.And
contains json data in Schema column.Even i have tried without select
Schema Column in query taking more time.

here is cache details,query and model class details.
Model class

public class CommonFormsSchema
    {
        [QuerySqlField]
        public string SchemaId { get; set; }
        [QuerySqlField]
        public string SchemaName { get; set; }
        [QuerySqlField]
        public string Schema { get; set; }
        [QuerySqlField]
        public string Parent { get; set; }
        [QuerySqlField]
        public int Version { get; set; }
        [QuerySqlField]
        public string Tenant { get; set; }
        [QuerySqlField]
        public string Application { get; set; }
        [QuerySqlField]
        public string PageType { get; set; }
        [QuerySqlField]
        public string Page { get; set; }
        [QuerySqlField]
        public string CategoryType { get; set; }
        [QuerySqlField]
        public bool favorite { get; set; }
        [QuerySqlField]
        public string filename { get; set; }
        [QuerySqlField]
        public bool isDeleted { get; set; }
        [QuerySqlField]
        public string CreatedBy { get; set; }
        [QuerySqlField]
        public string ModifiedBy { get; set; }
        [QuerySqlField]
        public DateTime _CreatedDateTime { get; set; }
        public DateTime CreatedDateTime
        {
            get { return _CreatedDateTime; }
            set
            {
                _CreatedDateTime = DateTime.SpecifyKind(value != null ?
value : DateTime.UtcNow, DateTimeKind.Utc);
            }
        }

        [QuerySqlField]
        public DateTime _ModifiedDateTime { get; set; }
        public DateTime ModifiedDateTime
        {
            get { return _ModifiedDateTime; }
            set
            {
                _ModifiedDateTime = DateTime.SpecifyKind(value != null ?
value : DateTime.UtcNow, DateTimeKind.Utc);
            }
        }
        public CommonFormsSchema()
        {
            ModifiedDateTime = DateTime.SpecifyKind(DateTime.Now,
DateTimeKind.Utc);
            CreatedDateTime = DateTime.SpecifyKind(DateTime.Now,
DateTimeKind.Utc);
        }
    }

Cache details

var primaryKeysIndex = new string[] { "SchemaId" };
                    var queryEntity = new QueryEntity(typeof(string),
typeof(CommonFormsSchema)) {
                        Indexes = new
List<QueryIndex>(primaryKeysIndex.Count() + 1)
                        {
                            new QueryIndex(true,0,primaryKeysIndex)
                        }
                    };
                    var queryList = new List<QueryEntity> { queryEntity };
                    
                    var cacheName = "COMMONFORMSSCHEMA";
                    bool cacheMetrics = false;
                    
                    var cacheCfg = new CacheConfiguration(cacheName)
                    {
                        Name = cacheName,
                        CacheStoreFactory = new
ConstructionCommonFormSchemaCacheStoreFactory(_logger, connectionString),
                        KeepBinaryInStore = false,  // Cache store works
with deserialized data.
                        ReadThrough = true,
                        WriteThrough = true,
                        QueryEntities = queryList,
                        DataRegionName = "IgniteDataRegion",
                        EvictionPolicy = new LruEvictionPolicy
                        {
                            MaxSize = 1000000
                        },
                        EnableStatistics = cacheMetrics
                    };

                    // Create cache with given name, if it does not exist.
                    ICache<string, CommonFormsSchema> cache =
Ignite.GetOrCreateCache<string, CommonFormsSchema>(cacheCfg);

Query:

SELECT SchemaId, SchemaName,SCHEMA ,Parent, Application, _ModifiedDateTime
as ModifiedDateTime, Version, PageType, favorite, 
CategoryType, Tenant, IsDeleted FROM "COMMONFORMSSCHEMA".CommonFormsSchema 
WHERE SchemaId in (SELECT DISTINCT SchemaId from
"COMMONFORMSSCHEMA".CommonFormsSchema WHERE Tenant = 'constructiontest'
UNION SELECT DISTINCT SchemaId FROM "COMMONFORMSSCHEMA".CommonFormsSchema
WHERE SchemaName NOT IN 
(SELECT DISTINCT SchemaName FROM "COMMONFORMSSCHEMA".CommonFormsSchema WHERE
Tenant = 'constructiontest') AND (Tenant = 'All' OR Tenant IS NULL))

Ignite console Warnings:

[10:20:21,728][WARNING][long-qry-#40%ServerNode%][LongRunningQueryManager]
Query execution is too long [duration=48294ms, type=MAP,
distributedJoin=false, enforceJoinOrder=false, lazy=false, schema=PUBLIC,
sql='SELECT
__Z0.SCHEMAID __C0_0,
__Z0.SCHEMANAME __C0_1,
__Z0.SCHEMA __C0_2,
__Z0.PARENT __C0_3,
__Z0.APPLICATION __C0_4,
__Z0._MODIFIEDDATETIME __C0_5,
__Z0.VERSION __C0_6,
__Z0.PAGETYPE __C0_7,
__Z0.FAVORITE __C0_8,
__Z0.CATEGORYTYPE __C0_9,
__Z0.TENANT __C0_10,
__Z0.ISDELETED __C0_11
FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z0
WHERE __Z0.SCHEMAID IN( (SELECT DISTINCT
__Z1.SCHEMAID
FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z1
WHERE __Z1.TENANT = 'constructiontest')
UNION
(SELECT DISTINCT
__Z2.SCHEMAID
FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z2
WHERE ((__Z2.TENANT = 'All') OR (__Z2.TENANT IS NULL)) AND (NOT
(__Z2.SCHEMANAME IN( SELECT DISTINCT
__Z3.SCHEMANAME
FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z3
WHERE __Z3.TENANT = 'constructiontest' )))) )', plan=SELECT
    __Z0.SCHEMAID AS __C0_0,
    __Z0.SCHEMANAME AS __C0_1,
    __Z0.SCHEMA AS __C0_2,
    __Z0.PARENT AS __C0_3,
    __Z0.APPLICATION AS __C0_4,
    __Z0._MODIFIEDDATETIME AS __C0_5,
    __Z0.VERSION AS __C0_6,
    __Z0.PAGETYPE AS __C0_7,
    __Z0.FAVORITE AS __C0_8,
    __Z0.CATEGORYTYPE AS __C0_9,
    __Z0.TENANT AS __C0_10,
    __Z0.ISDELETED AS __C0_11
FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z0
    /* COMMONFORMSSCHEMA.COMMONFORMSSCHEMA_SCHEMAID_DESC_IDX: SCHEMAID
IN((SELECT DISTINCT
        __Z1.SCHEMAID
    FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z1
        /++ COMMONFORMSSCHEMA.COMMONFORMSSCHEMA.__SCAN_ ++/
        /++ scanCount: 265 ++/
    WHERE __Z1.TENANT = 'constructiontest')
    UNION
    (SELECT DISTINCT
        __Z2.SCHEMAID
    FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z2
        /++ COMMONFORMSSCHEMA.COMMONFORMSSCHEMA.__SCAN_ ++/
        /++ scanCount: 167 ++/
    WHERE ((__Z2.TENANT = 'All')
        OR (__Z2.TENANT IS NULL))
        AND (NOT (__Z2.SCHEMANAME IN(
        SELECT DISTINCT
            __Z3.SCHEMANAME
        FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z3
            /++ COMMONFORMSSCHEMA.COMMONFORMSSCHEMA.__SCAN_ ++/
        WHERE __Z3.TENANT = 'constructiontest')))))
     */
    /* scanCount: 66 */
WHERE __Z0.SCHEMAID IN(
    (SELECT DISTINCT
        __Z1.SCHEMAID
    FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z1
        /* COMMONFORMSSCHEMA.COMMONFORMSSCHEMA.__SCAN_ */
        /* scanCount: 265 */
    WHERE __Z1.TENANT = 'constructiontest')
    UNION
    (SELECT DISTINCT
        __Z2.SCHEMAID
    FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z2
        /* COMMONFORMSSCHEMA.COMMONFORMSSCHEMA.__SCAN_ */
        /* scanCount: 167 */
    WHERE ((__Z2.TENANT = 'All')
        OR (__Z2.TENANT IS NULL))
        AND (NOT (__Z2.SCHEMANAME IN(
        SELECT DISTINCT
            __Z3.SCHEMANAME
        FROM COMMONFORMSSCHEMA.COMMONFORMSSCHEMA __Z3
            /* COMMONFORMSSCHEMA.COMMONFORMSSCHEMA.__SCAN_ */
        WHERE __Z3.TENANT = 'constructiontest'))))), node=TcpDiscoveryNode
[id=51d4e538-f8ec-4dbf-bb38-202e08cbbaa3,
consistentId=eaa71b21-a780-4872-aeb9-9c196593b1a3, addrs=ArrayList
[0:0:0:0:0:0:0:1, 127.0.0.1, 192.168.1.101,
2405:205:1407:2687:550e:d25c:580:e1cc,
2405:205:1407:2687:fc8f:a9d6:3040:d7b5], sockAddrs=HashSet
[/2405:205:1407:2687:fc8f:a9d6:3040:d7b5:47500,
LAPTOP-VRGB6LP6/192.168.1.101:47500,
/2405:205:1407:2687:550e:d25c:580:e1cc:47500, /0:0:0:0:0:0:0:1:47500,
/127.0.0.1:47500], discPort=47500, order=1, intOrder=1,
lastExchangeTime=1609852079577, loc=true, ver=2.9.1#20201203-sha1:adcce517,
isClient=false], reqId=27, segment=0]

What might be the cause behind query taking more time to return result.what
i have to modify so that i can get result with performance for the above
query.?

Thanks.




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

[Alexandr Shapkin]

Hi Siva,

Have you checked the SQL tuning documentation?

https://www.gridgain.com/docs/latest/perf-troubleshooting-guide/sql-tuning



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

[siva]

Hi Alexandr Shapkin,
After seen documentation i am quite not understanding with performance
tuning in term of above query.
could you please if possible suggest me in above query where i am lagging in
query.And what is the other way or possibility i should try that.
Any help is very appreciate.

Thanks.



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