Member Avatar for mikeybware

I currently have some code that gets the overall top 25 frame sales, which is as follows:

Dim FrameCountSelectStr As String = "SELECT TOP 25 Count(Glasses.OrderID) AS GlassesCount, Glasses.Manufacturer, Glasses.FrameName FROM(Glasses) WHERE FrameName <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# GROUP BY Glasses.Manufacturer, Glasses.FrameName ORDER BY Count(Glasses.OrderID) DESC"

What I would also like to do is get the top 25 frame sales for each DISTINCT company and group the results by company (and order by count). I tried the following but I am not having any luck:

Dim FrameCountSelectStr As String = "SELECT TOP 25 Count(Glasses.OrderID) AS GlassesCount, Glasses.Manufacturer, Glasses.FrameName FROM(Glasses) WHERE (SELECT DISTINCT Glasses.Manufacturer FROM(Glasses) WHERE FrameName <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# GROUP BY Glasses.Manufacturer, Glasses.FrameName ORDER BY Count(Glasses.OrderID) DESC"

Maybe I am just tired or getting confused, but any pointers in the right direction would be much appreciated.

  • 3 Contributors
  • 6 Replies
  • 336 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

A single-query pattern that aggregates once and then ranks per manufacturer is the fastest way to replace the per-manufacturer loop you described. If your server supports window functions (MySQL 8.0+), compute the frame counts, assign a row number partitioned by Manufacturer ordered by the count, and then keep rows where the rank ≤ 25. That avoids repeating full scans for each manufacturer.

-- MySQL 8+ (use prepared parameters for the date range and dispenser)
SELECT Manufacturer, FrameName, Colour, ADim, cnt
FROM (
  SELECT Manufacturer, FrameName, Colour, ADim,
         COUNT(*) AS cnt,
         ROW_NUMBER() OVER (PARTITION BY Manufacturer ORDER BY COUNT(*) DESC) AS rn
  FROM Glasses
  WHERE FrameName <> '' 
    AND Manufacturer <> '' 
    AND OrderDate >= ? 
    AND OrderDate < ? 
    AND Dispenser = ?
  GROUP BY Manufacturer, FrameName, Colour, ADim
) t
WHERE rn <= 25
ORDER BY Manufacturer, cnt DESC;

If you’re on an older MySQL without window functions, pre-aggregate once and use user variables to produce a per-manufacturer rank in a single pass (faster than looping):

-- MySQL < 8 using user variables
SET @manu := '', @r := 0;
SELECT Manufacturer, FrameName, Colour, ADim, cnt
FROM (
  SELECT p.*,
         @r := IF(@manu = p.Manufacturer, @r + 1, 1) AS rn,
         @manu := p.Manufacturer
  FROM (
    SELECT Manufacturer, FrameName, Colour, ADim, COUNT(*) AS cnt
    FROM Glasses
    WHERE FrameName <> '' 
      AND Manufacturer <> '' 
      AND OrderDate >= ? 
      AND OrderDate < ? 
      AND Dispenser = ?
    GROUP BY Manufacturer, FrameName, Colour, ADim
    ORDER BY Manufacturer, cnt DESC
  ) p
) ranked
WHERE rn <= 25;

Practical tips: run EXPLAIN to check plans; create indexes on the filtering columns (for example an index on (Dispenser, OrderDate) and a separate index on Manufacturer or a tuned composite index depending on selectivity); avoid selecting non-aggregated columns you don't need (or include them in GROUP BY / use aggregation) so ONLY_FULL_GROUP_BY won’t bite; and use parameterized/prepared statements from your VB code for safety and plan reuse. This consolidates your work into one server-side pass and addresses the performance issues and highlighted while replacing the iterative approach you used, .

Recommended Answers

All 6 Replies

Member Avatar for mikeybware

I will give that article a read and see if I can come up with a solution.

Member Avatar for mikeybware

I was able to perform the task in 2 separate SELECT queries but it is pretty slow to load all of the data the way I came up with. I am pretty sure it can be combined into 1 quick efficient query but I am not able to get any combination working.

My first SELECT statement is: "SELECT DISTINCT Manufacturer FROM Glasses WHERE FrameName <> '' AND Manufacturer <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# AND Dispenser ='" & ComboBox1.Text & "' ORDER BY Manufacturer ASC"

I then iterate through the returned dataset rows: For Each row As DataRow In ManuDS.Tables(0).Rows

And throw this SELECT statement in there: "SELECT TOP 25 Count(Glasses.OrderID) AS GlassesCount, Glasses.Manufacturer, Glasses.FrameName, Glasses.Colour, Glasses.ADim FROM(Glasses) WHERE FrameName <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# AND Manufacturer = '" & row.Item("Manufacturer").ToString & "' AND Dispenser ='" & ComboBox1.Text & "' GROUP BY Glasses.Manufacturer, Glasses.FrameName ORDER BY Count(Glasses.OrderID) DESC"

Again, this gets the job done, but it does take some time to load all of the data as I am running this query for multiple Manufacturers.

Any help on getting this into 1 efficient query would be much appreciated.

Member Avatar for mikeybware

Manufacturers is not indexed. I will definitely do that to improve performance. Thank you for all of your help.

Member Avatar for Reverend Jim

You should consider using parameterized queries. Not only are they more secure, they are much easier to read and debug.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.