dotnet / dotnet/machinelearning-modelbuilder
"Execution Timeout Expired" on Large SQL Dataset
- Dominant language
- Dockerfile
- Stars
- 285
- Forks
- 66
- PR merge metrics
- No merged PRs in 30d
Description
- Model Builder or CLI Version:
17.17.0.2332602
- Visual Studio Version (if applicable):
Microsoft Visual Studio Community 2022 (64-bit) - Current
Version 17.5.4
- On which page of the tutorial did you run into an issue (URL): N/A this is a bug in the Model Builder itself.
- Clear description of the problem:
Screen capture 1 shows a generic timeout error when attempting to connect to SQL data source. This only occurs on very large datasets. For example, the timeout occurring on the desired data training set has 3936051 records in the table. I can switch to a table with a smaller number of records and everything loads just fine. I don't see a way to change the timeout time other than in the Advanced Settings when connecting to the database, but I changed the Initialization >> Command Timeout value to 3600 and it still errors within the same amount of time so that doesn't appear to affect this timeout.
Screen capture 2 shows that I can go into the "Advanced data options" and manually set my label in the UI, but then throws an error when saving. Screen capture 3 shows the selected label was saved regardless.
Screen capture 4 shows that I can select a different table from the database with smaller number of records and it loads just fine to proceed to the next steps.
To summarize the problem:
1) Using SQL database as source with very large dataset fails to load
2) "Data Preview, Processing data...." runs indefinitely
3) "Next step" button never enables due to these issues, so I can never finish the model unless I use a much smaller dataset which is not desirable for my application
Steps to reproduce the behavior:
1) Select value prediction scenario
2) Select local training environment
3) Input >> SQL Server
4) Data source >> Microsoft SQL Server
5) Authenticate server and select database
6) Select table with very large dataset (almost 4 million records in my case) and wait for the following error typically after ~30 seconds: "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
7) Select a different table with smaller dataset to verify the connection is still active and working pointing to the large dataset as the issue
**Expected behavior**
The expected behavior is that "Data Preview" doesn't timeout that soon, that the preview of the large dataset is loaded, that I can then select from the "Column to predict (Label)" dropdown list which column should be the label, and that the "Next step" button enables after selecting the label.
Screen capture 1

Full stack trace:
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.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.ML.ModelBuilder.DataProviders.SQLDataProvider.d__15.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.ML.ModelBuilder.ToolWindows.DataTextViewModel.d__146.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.ML.ModelBuilder.ToolWindows.TextDataControl.<>c__DisplayClass4_0.<b__0>d.MoveNext()
Screen capture 2

Full stack trace:
at System.ThrowHelper.ThrowArgumentNullException(ExceptionArgument argument)
at System.Collections.Generic.Dictionary`2.FindEntry(TKey key)
at System.Collections.Generic.Dictionary`2.TryGetValue(TKey key, TValue& value)
at Microsoft.ML.ModelBuilder.Utils.Utilities.GetTrainValidateDataSplitRatio(String ratio)
at Microsoft.ML.ModelBuilder.ViewModels.AdvancedDataOptionsViewModel.d__64.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.ML.ModelBuilder.ToolWindows.AdvancedDataOptions.AdvancedDataCleaningDialog.<>c__DisplayClass3_0.<b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.VisualStudio.Threading.JoinableTask.CompleteOnCurrentThread()
at Microsoft.ML.ModelBuilder.ToolWindows.AdvancedDataOptions.AdvancedDataCleaningDialog.SaveButton_Click(Object sender, RoutedEventArgs e)
Screen capture 3

Screen capture 4

**Additional context**
Consistently reproduceable following the steps above. It only occurs trying to use the tables with many 100k's of records.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.