You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by siva <si...@bizruntime.com> on 2021/01/06 06:16:10 UTC
Query execution is too long
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/
Re: Query execution is too long
Posted by siva <si...@bizruntime.com>.
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/
Re: Query execution is too long
Posted by Alexandr Shapkin <le...@gmail.com>.
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/