Time-out error using an Exact Online query in the Invantive query tool?

Question

I'm using this query:

select * 
from   MailMessagesReceived mmd
inner  
join   MailMessageAttachments mmt
on     mmd.ID = mmt.ID
Where  mmd.created > '2017-01-01'
And    mmd.SenderMailbox = '[email protected]'
And    mmd.RecipientStatusDescription = 'Prepared'

And i get a time out error without message code. It reads "Er is een time-out opgetreden voor de bewerking." The following details are attached to the error:

select * from MailMessagesReceived
inner join MailMessageAttachments
on MailMessagesReceived.ID = MailMessageAttachments.ID
Where MailMessagesReceived.created > "2017-01-01"
And MailMessagesReceived.SenderMailbox = "[email protected]"
And MailMessagesReceived.RecipientStatusDescription = "Prepared"

Type: System.Net.WebException
bij System.Net.HttpWebRequest.GetResponse()
bij Invantive.Data.ODataProvider.DoRequest(HttpWebRequest request, String url, String partition, String& returnUrl, Dictionary`2& headers) in File161:regel 3666
bij Invantive.Data.ODataProvider.GetInternal(String relativeUrl, String mimeType, String partition, Boolean allowFromCache, String& returnUrl, Dictionary`2& headers, Boolean& fromCache) in File161:regel 3001
bij Invantive.Data.ODataProvider.Get[T](String relativeUrl, Dictionary`2& headers, String& fullUrl, Boolean& resultFromCache, String partition, Boolean allowFromCache, String mimeType) in File161:regel 2880
bij Invantive.Data.ODataProvider.ReadDataFromServiceResponse(List`1 rows, String basePath, List`1 allFieldTypes, String url, String partition, ObjectDefinition objectDefinition, QueryObject queryObject, Int32& pagesRetrieved, Int32& rowsRetrieved) in File161:regel 1102
bij Invantive.Data.ODataProvider.FetchDataOnePartition(String serviceUrl, String partition, List`1 rows, String basePath, List`1 allFieldTypes, ObjectDefinition objectDefinition, QueryObject queryObject, ParameterList parameters) in File161:regel 1021
bij Invantive.Data.ODataProvider.<>c__DisplayClass72_3.<Fetch>b__2(String partition) in File161:regel 877
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---
bij Invantive.Data.ODataProvider.Fetch(EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount, ResultSet& resultSet, Int32& totalNumberOfRows) in File161:regel 914
bij Invantive.Data.ExactOnlineProvider.Fetch(EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount, ResultSet& resultSet, Int32& totalNumberOfRows) in File298:regel 909
bij Invantive.Data.ConnectionManager.ExecuteProviderFetch(EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount, Int32& totalNumberOfRows, String& handlingPath) in File39:regel 3190
bij Invantive.Data.ConnectionManager.Fetch(EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount) in File39:regel 1406
bij Invantive.Sql.DataSourceOrFunctionTree.<GetDataFromDataContainer>d__29.MoveNext() in File120:regel 315
bij System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
bij System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
bij Invantive.Sql.FirehoseResultSet.<Iterator>d__54.MoveNext() in File133:regel 547
bij System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
bij System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
bij Invantive.Sql.FirehoseResultSet.<Iterator>d__54.MoveNext() in File133:regel 559
bij System.Linq.Buffer`1..ctor(IEnumerable`1 source)
bij System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
bij Invantive.Sql.JoinIterator.<JoinInternal>d__13.MoveNext() in File135:regel 481
bij Invantive.Sql.FilterIterator.<Iterator>d__3.MoveNext() in File132:regel 92
bij Invantive.Sql.ChainedFirehose.<Iterator>d__11.MoveNext() in File130:regel 81
bij Invantive.Sql.SelectListIterator.<Iterator>d__7.MoveNext() in File138:regel 119
bij System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
bij System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
bij Invantive.Sql.QueryPlan.Fetch() in File140:regel 91
bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath) in File39:regel 4033
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---
bij System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath) in File39:regel 4062
bij Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File39:regel 2086
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---
bij System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
bij Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File39:regel 2098
bij Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File29:regel 134
bij Invantive.Producer.Windows.Forms.QueryTool.ExecuteStatement(IProgressNotifier notifier, String statement, ParameterList bindVariables, Boolean showResultsInGrid, Boolean showStatistics, Boolean memorizeStatisticsInSqlHistory, Boolean allowPaging) in File948:regel 2847
bij Invantive.Producer.Windows.Forms.QueryTool.FetchResultsFromSql() in File948:regel 2430

I'm trying to get an overview of the 'Scan en Herken' (i.e. Scanning and recognition of invoices) inbox in Exact Online. This inbox contains invoices and computer generated account entries, each of which is either:

• ready to be processed (green),
• needs a few alterations (yellow)
• or needs to be handled mannualy (red).

We want to know specifically how many invoices are in each of these three different states. For those who need alterations or manual corrections we want to know what the errors are.

With the query I was using I was just digging around to find out what data is contained in the MailMessageAttachments table. I don't know whether I'm digging in the correct place, so any advice on the subject would be nice. Furthermore I would like to know why my SQL code raises a time out.

Answer 1

There are some possible improvements. The REST API Mail messages (JSON) of Exact Online has recently been restructured to perform better, but nonetheless is not the fastest one around.

Please make sure first of all that you select only the divisions that you want to work with, for instance using:

use 123456,345678,56789

instead of using:

use all

Start of with a small division with some sample data and when the queries works improve performance by testing it on larger divisions.

Also, you might want to make sure that no implicit data type conversions take place, so prefer:

date-field > to_date('20170101', 'yyyymmdd')

to

date-field > '2017-01-01'

In this query it will probably not hurt.

It is better to query on status code instead of description. Descriptions vary per country and even per user. And a code might have been indexed, whereas a translatable description not.

Also, not all mail message will have an attachment, so it is better to use a left outer join.

Also, the MailMessageAttachments table can be a HUGE table (many documents included in binary format, especially when people use a gray scale or color scanner). Each row can download multiple megabytes, so it is better to only join it when absolutely needed.

Finally, you can have the query stop running after a limited number of rows have been retrieved using 'top' or 'limit' syntax.

Resulting query:

select * 
from   MailMessagesReceived mmd
left 
outer
join   MailMessageAttachments mmt
on     mmd.ID = mmt.ID
Where  mmd.created > to_date('20170101', 'yyyymmdd')
And    mmd.SenderMailbox = '[email protected]' --     '[email protected]'
and    mmd.recipientstatus = 20 /* Open. Etc, look up for Prepared. Enables index use. */
limit 50

When run on 30 small divisions using set requests-parallel-max 8, it takes approximately 1 minute to the row.

PS. Note that you can find the actual REST and XML URL calls and their duration using select * from exactonlinerest..sessionios.