Querying Northwind with Group By bug fixed
In this post:
http://blogs.msdn.com/calvin_hsia/archive/2007/08/17/install-northwind-for-sql-express-and-use-visual-studio-and-dlinq-to-query-it.aspx
I mentioned how a bug with Group By and composite keys made more local calculations. Using the AsEnumerable operator caused the query to execute locally.
I now have a build of Visual Studio that has the bug fixed and I modified the query to remove the AsEnumerable().
The performance is very quick, as it’s all executed on the back end, as seen by this log:
SELECT [t7].[CustomerID], [t7].[CompanyName], [t7].[value] AS [CustTotal], [t7].[value2] AS [PctTotal]
FROM (
SELECT SUM([t6].[value2]) AS [value], SUM([t6].[value]) AS [value2], [t6].[CustomerID], [t6].[CompanyName]
FROM (
SELECT (@p1 * (CONVERT(Real,(CONVERT(Decimal(29,4),CONVERT(Int,[t3].[Quantity]))) * [t3].[UnitPrice])) * (@p2 - [t3].[Discount])) / ((
SELECT SUM([t5].[value])
FROM (
SELECT (CONVERT(Real,(CONVERT(Decimal(29,4),CONVERT(Int,[t4].[Quantity]))) * [t4].[UnitPrice])) * (@p3 - [t4].[Discount]) AS [value]
FROM [dbo].[Order Details] AS [t4]
) AS [t5]
)) AS [value], [t3].[CustomerID], [t3].[CompanyName], [t3].[value] AS [value2]
FROM (
SELECT (CONVERT(Real,(CONVERT(Decimal(29,4),CONVERT(Int,[t2].[Quantity]))) * [t2].[UnitPrice])) * (@p0 - [t2].[Discount]) AS [value], [t2].[Quantity], [t2].[UnitPrice], [t2].[Discount], [t0].[CustomerID], [t0].[CompanyName]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
INNER JOIN [dbo].[Order Details] AS [t2] ON [t1].[OrderID] = [t2].[OrderID]
) AS [t3]
) AS [t6]
GROUP BY [t6].[CustomerID], [t6].[CompanyName]
) AS [t7]
ORDER BY [t7].[value2] DESC, [t7].[CustomerID]
-- @p0: Input Real (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Real (Size = 0; Prec = 0; Scale = 0) [100]
-- @p2: Input Real (Size = 0; Prec = 0; Scale = 0) [1]
-- @p3: Input Real (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20828.0
The entire code is posted here:
Imports System.IO
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.Width = 1024
Me.Height = 768
Dim NWind As New DataClasses1DataContext
NWind.Log = Console.Out
Dim q = From cust In NWind.Customers _
Join ord In NWind.Orders On cust.CustomerID Equals ord.CustomerID _
Join det In NWind.Order_Details On ord.OrderID Equals det.OrderID _
Group By cust.CustomerID, cust.CompanyName Into _
CustTotal = Sum(det.Quantity * det.UnitPrice * (1 - det.Discount)), _
PctTotal = Sum(100 * (det.Quantity * det.UnitPrice * (1 - det.Discount)) / _
Aggregate d2 In NWind.Order_Details _
Into Sum(d2.Quantity * d2.UnitPrice * (1 - d2.Discount))) _
Order By CustomerID _
Select CustomerID, CompanyName, CustTotal, PctTotal _
Order By PctTotal Descending
Browse(q)
End Sub
Sub Browse(Of t)(ByVal seq As IEnumerable(Of t))
Dim GridView As New DataGridView
GridView.Width = Me.Width
GridView.Height = Me.Height
Me.Controls.Add(GridView)
Dim pl = New List(Of t)(seq)
GridView.DataSource = pl
Me.Text = pl.Count.ToString
GridView.Dock = DockStyle.Fill
GridView.AutoResizeColumns()
End Sub
End Class
