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