MSSQL Fatel error: The query processor ran out of internal resources and could not produce a query plan

364 Views

Hi, I am using hybris-commerce-suite-5.4.0.3 + Microsoft SQL server 2014 database. Getting the fatal error during content catalog synchronization/system update where as it is working fine when connected to HSQL DB. Even I have tried to remove all the relations defined in my core-items.xml file but still getting errors. =========== Pl refer below error description =============

[DefaultSetupSyncJobService] Created cronjob [000000VW] to synchronize catalog [myprojectContentCatalog] staged to online version. INFO | jvm 1 | main | 2016/02/10 20:22:06.301 | INFO [hybrisHTTP20] [DefaultSetupSyncJobService] Configuring full version sync INFO | jvm 1 | main | 2016/02/10 20:22:22.805 | ERROR [Thread-141] [SyncSchedulerCallableBase] Exception The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. occurred during executing query INFO | jvm 1 | main | 2016/02/10 20:22:24.607 | (SELECT {p1:pk},{p2:pk} FROM {CMSItem AS p1 JOIN CMSItem AS p2 ON {p1:catalogVersion}=?srcVer AND {p2:catalogVersion}=?tgtVer AND {p1:uid}={p2:uid} } WHERE NOT EXISTS ({{SELECT {pk} FROM {ItemSyncTimestamp AS ts} WHERE {ts:syncJob}=0 AND {ts:targetVersion}=?tgtVer AND {ts:sourceVersion}=?srcVer AND ({ts:sourceItem}={p1:pk} )}}) AND NOT EXISTS ({{ SELECT {pk} FROM {ItemSyncTimestamp AS ts} WHERE {ts:syncJob}=0 AND {ts:targetVersion}=?tgtVer AND {ts:sourceVersion}=?srcVer AND ({ts:targetItem}={p2:pk} )}})ORDER BY {p1:creationtime} ASC, {p1:pk} ASC) with arguments (tgtVer,srcVer), INFO | jvm 1 | main | 2016/02/10 20:22:24.609 | values(myprojectContentCatalog/Online(8796093252185),myprojectContentCatalog/Staged(8796093219417)) INFO | jvm 1 | main | 2016/02/10 20:22:24.620 | INFO | jvm 1 | main | 2016/02/10 20:22:24.620 | de.hybris.platform.jalo.JaloSystemException: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.[HY--1] INFO | jvm 1 | main | 2016/02/10 20:22:24.621 | at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.handleError(JDBCQuery.java:126) INFO | jvm 1 | main | 2016/02/10 20:22:24.621 | at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:74) INFO | jvm 1 | main | 2016/02/10 20:22:24.621 | at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.callImpl(SyncSchedulerCallableBase.java:120) INFO | jvm 1 | main | 2016/02/10 20:22:24.621 | at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:96) INFO | wrapper | main | 2016/02/10 20:22:24.621 | Wrapper Process has not received any CPU time for 1 seconds. Extending timeouts. INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:1) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at java.util.concurrent.FutureTask.run(FutureTask.java:262) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at de.hybris.platform.core.TenantAwareThreadFactory$1.run(TenantAwareThreadFactory.java:160) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) INFO | jvm 1 | main | 2016/02/10 20:22:24.628 | at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) INFO | jvm 1 | main | 2016/02/10 20:22:24.628 | at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | at de.hybris.platform.jdbcwrapper.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:194) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:70) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | ... 7 more INFO | jvm 1 | main | 2016/02/10 20:22:24.630 | INFO [hybrisHTTP20] [Initialization] Error creating sample data for myprojectinitialdata. See console output.