Developer forum

Forum » Development » LoadVariantInfo times out on group by

LoadVariantInfo times out on group by

E-mail notifications
Lasse Larsen
Reply
Posted on 30/05/2022 09:41:26

In DW 9.9.6 in a solution with two shops and 500k+ products.

When trying to build a product index, LoadVariantInfo times out on the expression: 

SELECT VariantOptionsProductRelationProductID
FROM EcomVariantOptionsProductRelation LEFT JOIN EcomProducts
    ON VariantOptionsProductRelationProductID = ProductID AND VariantOptionsProductRelationVariantID = ProductVariantID
    WHERE ProductID IS NULL
    GROUP BY VariantOptionsProductRelationProductID

The expression takes 1:10 to run, unless i remove the group by, it then executes instantly.

Shop A on the solution have 77000 entries on the VariantOptionsProductRelationProductID and shop B have 0. Even though shop B have no entries on the table its only when its products are loaded into EcomProducts. Shop B dont have any variants. If i prefix all the product ids in shop B with a random char, the expression executes instantly.

 

 

 

Running it with group by

Running it without group by:

Log.Log from index diagnostics:

2022-05-30 09:35:44.378: Running.
2022-05-30 09:35:44.395: Fetching build definition.
2022-05-30 09:35:44.395: Fetched build definition. 'Dynamicweb.Ecommerce.Indexing.ProductIndexBuilder'.
2022-05-30 09:35:44.411: Preparing schema.
2022-05-30 09:35:44.411: Prepared schema.
2022-05-30 09:35:44.426: Creating index writer.
2022-05-30 09:35:44.426: Created index writer: 'Dynamicweb.Indexing.Lucene.LuceneIndexWriter'.
2022-05-30 09:35:44.441: Instance availability (B): False.
2022-05-30 09:35:44.441: Starting build process.
2022-05-30 09:35:44.457: Dynamicweb.Ecommerce.Indexing.ProductIndexBuilder building using Dynamicweb.Indexing.Lucene.LuceneIndexWriter... Action: 'Full', Resume: 'False'
2022-05-30 09:35:44.457: Opening index writer.
2022-05-30 09:35:44.545: Opened index writer to overwrite index
2022-05-30 09:35:44.545: Opening database connection.
2022-05-30 09:35:44.556: BulkSize: '500'
2022-05-30 09:35:44.556: MaxProductsToIndex: '2147483647'
2022-05-30 09:35:44.566: OnlyIndexActiveProducts: 'True'
2022-05-30 09:35:44.566: SkipGrouping: 'False'
2022-05-30 09:35:44.566: SkipGroupSorting: 'True'
2022-05-30 09:35:44.586: SkipRelatedProducts: 'True'
2022-05-30 09:35:44.586: SkipExtenders: 'True'
2022-05-30 09:35:44.598: SkipAllExtendedFields: 'True'
2022-05-30 09:35:44.598: SkipCategoryFields: 'True'
2022-05-30 09:35:44.598: HandleInheritedCategoryValues: 'True'
2022-05-30 09:35:44.616: DoNotStoreDefaultFields: 'False'
2022-05-30 09:35:44.616: DoNotAnalyzeDefaultFields: 'False'
2022-05-30 09:35:44.629: EmptyStringReplacement: ''
2022-05-30 09:35:44.629: SkipImages: 'True'
2022-05-30 09:35:44.629: ShopsToIndex: ''
2022-05-30 09:35:44.647: SkipPrices: 'True'
2022-05-30 09:35:44.647: SkipDetailImages: 'True'
2022-05-30 09:35:44.660: SkipImagePatternImages: 'True'
2022-05-30 09:35:45.138: Loading product group relation information.
2022-05-30 09:36:33.924: Loaded product group relation information.
2022-05-30 09:36:33.924: Loading group relation information.
2022-05-30 09:36:33.971: Loaded group relation information.
2022-05-30 09:36:33.971: Loading product group relation information.
2022-05-30 09:36:34.015: Loaded product group relation information.
2022-05-30 09:36:34.019: Loading product groups.
2022-05-30 09:36:34.059: Loaded product groups.
2022-05-30 09:36:34.059: Loading campaign information.
2022-05-30 09:36:34.099: Loaded campaign information.
2022-05-30 09:36:34.099: Loading variant information.
2022-05-30 09:37:07.541: Product index builder experienced a fatal error.. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()    at System.Data.SqlClient.SqlDataReader.get_MetaData()    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)    at System.Data.SqlClient.SqlCommand.ExecuteReader()    at Dynamicweb.Ecommerce.Indexing.ProductIndexBuilder.LoadVariantInfo(SqlConnection connection, Tracker tracker)    at Dynamicweb.Ecommerce.Indexing.ProductIndexBuilder.Build(IIndexWriter writer, Tracker tracker) ClientConnectionId:9ba05bfd-5be5-475a-bcd7-7b65ea56eff7 Error Number:-2,State:0,Class:11
2022-05-30 09:37:07.541: Product index builder experienced a fatal error.
2022-05-30 09:37:07.595: Failed (0 / 552794).
2022-05-30 09:37:07.596: indexer.Build(writer, tracker) failed. tracker.Status.State = tracking dot state dot failed.
2022-05-30 09:37:07.596: Closing index writer, just trying...
2022-05-30 09:37:07.642: Closed index writer successfully.

 

 

 


Replies

 
Nicolai Pedersen
Posted on 30/05/2022 09:53:59
Reply

You probably have to fix it with an index on the table - have a look at the execution plan - my guess is that you have a table scan that causes this.

 

Should look something like this - do you have the same indexes on your table.

 
Lasse Larsen
Posted on 30/05/2022 10:14:04
Reply

It dosnt look like i have the same indxes, but it is using an index scan.

 
Nicolai Pedersen
Posted on 30/05/2022 10:18:01
Reply

ok.

Try adding the indexes:

/****** Object:  Index [EcomVariantOptionsProductRelation$EcomVariantOptionsProductRelationID]    Script Date: 30/05/2022 10.17.17 ******/

CREATE NONCLUSTERED INDEX [EcomVariantOptionsProductRelation$EcomVariantOptionsProductRelationID] ON [dbo].[EcomVariantOptionsProductRelation]

(

 [VariantOptionsProductRelationVariantID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

GO


/****** Object:  Index [EcomVariantOptionsProductRelation$EcomVariantOptionsProductRelationGroupID]    Script Date: 30/05/2022 10.17.10 ******/

CREATE NONCLUSTERED INDEX [EcomVariantOptionsProductRelation$EcomVariantOptionsProductRelationGroupID] ON [dbo].[EcomVariantOptionsProductRelation]

(

 [VariantOptionsProductRelationProductID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

GO
 
Lasse Larsen
Posted on 30/05/2022 13:07:11
Reply

I have tried adding the indexes, without the OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, unfortunately my management studio dosnt like that one, but execution still takes above a minute.

 

 
Lasse Larsen
Posted on 07/07/2022 15:25:55
Reply

Unfortunately we are still dealing with this proplem and are still unable to build our product index.

One solution could be to change the sql expression to something like:

SELECT VariantOptionsProductRelationProductID
FROM EcomVariantOptionsProductRelation
WHERE NOT EXISTS (SELECT *
   FROM EcomProducts
   WHERE VariantOptionsProductRelationProductID = ProductID AND VariantOptionsProductRelationVariantID = ProductVariantID)
GROUP BY VariantOptionsProductRelationProductID

Which runs mush faster and should produce the same result. (see images below)

Unfortunately it looks like we are unable to change the sql expression ourself.

 
Morten Snedker Dynamicweb Employee
Morten Snedker
Posted on 11/07/2022 15:24:27
Reply

Hi Lasse,

If you pass us a ticket at https://care.dynamicweb.com/support/login (or send an email to care@dynamicweb.com) with a link to this thread, we will look into it. Additionally it would be helpful if we could obtain a copy of the database you are using (by a link to the solution if it is hosted by us, or a download-link to the database).

BR
Snedker

 
Lasse Larsen
Posted on 08/08/2022 10:39:46
Reply

Hi Morten,

I have submitted a ticket, with the ticket number of 1795.

 

You must be logged in to post in the forum