Why would Count Query on IEnumerable with 550 Rows take so long (19s) to process?

[JasonUSA]

Good morning,

I have a VB.NET WinForms application which queries an Azure SQL DB. The following function calls a SQL Table Function and places the result in an IEnumerable of type Projects_GetAllUnitResult (I've included the definition of this object below):

Dim allProjects As IEnumerable(Of Projects_GetAllUniqueResult) = oAppMain.FPDataContext.Projects_GetAllUnique().AsEnumerable()

Partial Public Class Projects_GetAllUniqueResult

Private _ProjectID As System.Nullable(Of Integer)

Private _ClientName As String

Private _ProjectRef As String

Private _EventTypeID As System.Nullable(Of Integer)

Private _EventType As String

Private _VenueID As System.Nullable(Of Integer)

Private _VenueName As String

Private _EventDate As System.Nullable(Of Date)

Private _Delivery As System.Nullable(Of Integer)

Private _UserID As System.Nullable(Of Integer)

Private _IsBooked As System.Nullable(Of Integer)

Public Sub New()
MyBase.New
End Sub

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_ProjectID", DbType:="Int")> _
Public Property ProjectID() As System.Nullable(Of Integer)
Get
Return Me._ProjectID
End Get
Set
If (Me._ProjectID.Equals(value) = false) Then
Me._ProjectID = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_ClientName", DbType:="VarChar(255)")> _
Public Property ClientName() As String
Get
Return Me._ClientName
End Get
Set
If (String.Equals(Me._ClientName, value) = false) Then
Me._ClientName = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_ProjectRef", DbType:="VarChar(10)")> _
Public Property ProjectRef() As String
Get
Return Me._ProjectRef
End Get
Set
If (String.Equals(Me._ProjectRef, value) = false) Then
Me._ProjectRef = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_EventTypeID", DbType:="Int")> _
Public Property EventTypeID() As System.Nullable(Of Integer)
Get
Return Me._EventTypeID
End Get
Set
If (Me._EventTypeID.Equals(value) = false) Then
Me._EventTypeID = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_EventType", DbType:="VarChar(366)")> _
Public Property EventType() As String
Get
Return Me._EventType
End Get
Set
If (String.Equals(Me._EventType, value) = false) Then
Me._EventType = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_VenueID", DbType:="Int")> _
Public Property VenueID() As System.Nullable(Of Integer)
Get
Return Me._VenueID
End Get
Set
If (Me._VenueID.Equals(value) = false) Then
Me._VenueID = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_VenueName", DbType:="VarChar(255)")> _
Public Property VenueName() As String
Get
Return Me._VenueName
End Get
Set
If (String.Equals(Me._VenueName, value) = false) Then
Me._VenueName = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_EventDate", DbType:="Date")> _
Public Property EventDate() As System.Nullable(Of Date)
Get
Return Me._EventDate
End Get
Set
If (Me._EventDate.Equals(value) = false) Then
Me._EventDate = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_Delivery", DbType:="Int")> _
Public Property Delivery() As System.Nullable(Of Integer)
Get
Return Me._Delivery
End Get
Set
If (Me._Delivery.Equals(value) = false) Then
Me._Delivery = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_UserID", DbType:="Int")> _
Public Property UserID() As System.Nullable(Of Integer)
Get
Return Me._UserID
End Get
Set
If (Me._UserID.Equals(value) = false) Then
Me._UserID = value
End If
End Set
End Property

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_IsBooked", DbType:="Int")> _
Public Property IsBooked() As System.Nullable(Of Integer)
Get
Return Me._IsBooked
End Get
Set
If (Me._IsBooked.Equals(value) = false) Then
Me._IsBooked = value
End If
End Set
End Property
End Class



This returns 554 rows in a split second which is fantastic.

Things go down hill from here. Some very simple actions I perform on this IEnumerable object are extremely slow while others are lightening fast.

The first, and simplest, thing I want to do is check if the query returned any rows and so I implemented the following:

If allProjects.Any() Then

End If

and found this to be incredibly slow (19 seconds) and so tried each of the following methods but they all took a similar amount of time to execute:

Dim rowCount = allProjects.Count

Dim rowCount = Aggregate alp In allProjects Into Count()

Dim allProjectsList = AllProjects.ToList() 'This takes ~19 seconds
Dim rowCount = allProjectsList.Count 'This is instantaneous

Dim allProjectsList = AllProjects.ToList() 'This takes ~19 seconds
Dim rowCount = Aggregate alp In allProjectsList Into Count() 'This is instantaneous

Dim allProjectsQueryable = AllProjects.AsQueryable() 'This is instantaneous
Dim rowCount = Aggregate alp In allProjectsQueryable Into Count() 'This takes ~19 seconds


This isn't a one time deal. I thought that maybe the time was the object being enumerated into memory and, once done, any action performed on that object would be instantaneous but if I perform any of the above 2 times in a row the second occurrence takes just as long as the first.

Something surprising is that, once I've confirmed the table contains rows, I pass it into a function - along with another object populated via a different Table Function Query - which performs a Left Outer Join LINQ Query on these and returns the result. At this point in time that second object only contains 2 rows so the returned object only contains 2 rows but the LINQ query completes in a fraction of a second. How is it possible that a Join query can complete so quickly while a Count query takes 19 seconds?

Is there something I'm doing wrong that's resulting in this behavior? Is there an object I should convert the IEnumerable to such that it can be used throughout the application without the kinds of bottlenecks I'm describing above?

This is being performed on a laptop with Intel Core i7-6700HQ CPU @ 2.60GHz and with 7.2 GB of 16 GB RAM available. I don't know the ins & outs of the Diagnostic Tools in VS19 but I can see that when this 19 second process is occurring only 35MB of RAM is being consumed and 0% of the processor. Despite this the fan on my laptop kicks in so it's doing something that's generating some heat and I'm surprised this isn't being conveyed via the Diagnostic Tools:



Incidentally, running the application in Release Mode without Debugging is no quicker.

Thank you very much in advance for any help,

Jason.

Continue reading...