search

gsimardnet / PowerQueryNet

Run M Language (Power Query Formula Language) from anywhere.
MIT License
189 stars 32 forks source link

Adjust Timeout #35

Open alazyworkaholic opened 2 years ago

alazyworkaholic commented 2 years ago

A query with fairly complex logic that cleans some atrociously formatted Excel files and writes the result to SQL Server works for up to a few dozen files but times out when I try to process hundreds at a time. Here's the 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.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
   at PowerQueryNet.Service.PowerQueryService.OutputToSQL(PowerQueryCommand powerQueryCommand, DataTable dataTable) in Z:\GitHub\PowerQueryNet\Service\PowerQueryService.cs:line 137
   at PowerQueryNet.Service.PowerQueryService.Execute(PowerQueryCommand powerQueryCommand) in Z:\GitHub\PowerQueryNet\Service\PowerQueryService.cs:line 296
ClientConnectionId:6b4b230b-be66-4968-adb9-f5a8bf3a0e57
Error Number:-2,State:0,Class:11

Can you create an option to adjust the timeout to accommodate slow processes?

sio commented 2 years ago

Just a sidenote, which may or may not reflect the project author's opinion:

You do realise that this is an opensource project, right?

The author created this on their own time and put this out for everyone to use and modify for free. The last commit to master branch was in 2019. Original author may have long moved on to other interests and projects, or may be just busy with other parts of their life.

It's great that you took time to document all the issues you've written about today, thank you for that. But I suggest not to expect and definitely not to demand any action to be taken because of that.

It seems that you're invested significantly into Power Query ecosystem. You've obviously spent a lot of time trying to apply this software to your tasks. Maybe you would be interested in fixing the issues you've encountered? Submitting PRs is way cooler than just describing issues!

Are you using this software at work? If you're not skilled enough to fix the issues, maybe there are some software engineers in your company who can do that?

alazyworkaholic commented 2 years ago

Yes, I realize this is open source. Yes, I've just begun attempting to use this seriously at work. Yes, I'm aware this hasn't been updated in a few years.

Unfortunately, I lack both the skills to fix the issues myself and the pull at work to have one of our handful of developers bother.

I do hope that someone who can will submit a pull request. @gsimardnet last commented in this repo only a few months ago so maybe it's not entirely abandoned, but I'm certainly not demanding anything.

The best I can do until I learn to fix the issues, if I ever do, is log them here.

alazyworkaholic commented 2 years ago

It looks like the timeout is set by a registry value at Computer\HKEY_LOCAL_MACHINE\SOFTWARE\PowerQueryNet IpcTimeout that defaults to 30 seconds (00:30:00) (see lines 335,337,339 of PowerQueryNet/Service/PowerQueryService.cs).

I can load just over 12 MB or 30k rows from a CSV file before timeout but changing the registry value doesn't seem to matter. More than that and it times out after approximately one minute and it doesn't matter whether the IpcTimeout value is set to 11:00:00 or 00:00:02.